CODEWITHSUNDEEP
sql-servert-sql
Sergioet
Sergioet

Reputation: 1288

SQL aggregating functions - count distinct avoiding duplicates

I have the following table in SQL Server:

enter image description here

the Account are text values with different Account names. I can have the same user id over different accounts and dates.

I would like to get a query result that counts the number of unique session_ids and number of uniq user_ids, grouped by Account, keeping date as a column because I would like to send this to Power BI and have the user to specify begin and end dates.

However, if I use this query:

SELECT Date,
Account,
COUNT(DISTINCT(session_id)) number_session,
COUNT(DISTINCT(user_id)) number_users
FROM Table
GROUP BY Date, Account
ORDER BY Date

The users id are not goint to be unique, because if a user connects in multiple dates, it will be counted several times. The unique values are just calculated per Date.

How can I keep the structure of the above query but counting unique users and sessions?

Upvotes: 0

Views: 44

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You could use subquery:

SELECT Date,
      Account,
      COUNT(DISTINCT(session_id)) number_session,
      MAX(SELECT COUNT(DISTINCT user_id) 
          FROM table t2 WHERE t1.Account=t2.Account) AS number_users
FROM Table t1
GROUP BY Date, Account
ORDER BY Date

Upvotes: 1

Related Questions