Count of distinct columns alongside each record of distinct value of that column-CodePudding

I am new to SQL and am not sure if this is the right approach, but basically I am trying to get the count of distinct dates and put that beside the individual date

For eg

Date     Username Count(Distinct(Date))
20220721 jbravo    3
20220722 jbravo    3
20220723 jbravo    3
20220721 mario    7
20220722 mario    7
20220723 mario    7
20220724 mario    7 
20220725 mario    7
20220726 mario    7
20220727 mario    7

I know how to get count(distinct(date)) grouped by user, but how do I achieve the below since, if I do

select Date, Username, Count(Distinct(Date))
from table
group by Username

will throw an error that I need Date in the group by clause

If I add Date in the group by clause, obviously the distinct count becomes 1

CodePudding user response：

That is an aggregation issue. You can use common table expression

WITH t1 AS (
  SELECT username, COUNT(DISTINCT(dates)) AS dist_dates
  FROM tab1
  GROUP BY username
  )
SELECT tab1.dates, tab1.username, t1.dist_dates
FROM tab1
INNER JOIN t1
ON t1.username = tab1.username

You should be aware that the best option would be joining on ids. However, since you did not mention you have that, this solution works fine.

CodePudding user response：

GROUP BY x, y, z means you want one result row per x, y, z. group by username must be wrong, because you want more than one result row per username.

In fact you don't want to aggregate your rows at all, but show all rows as they are. You only want to add an aggregation information to these rows. This is done with an analytic function, which is an aggregation function in combination with an over clause that tells the DBMS over which rows you want the aggregation evaluation.

You haven't told us your DBMS. Here is the standard SQL query for this:

select
  date,
  username,
  count(distinct date) over (partition by username) as dates
from mytable
order by date, username;