CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Bagzli
Bagzli

Reputation: 6579

Select count of total records and also distinct records

I have a table such as this:

PalmId | UserId | CreatedDate
1      | 1      | 2018-03-08 14:18:27.077
1      | 2      | 2018-03-08 14:18:27.077
1      | 3      | 2018-03-08 14:18:27.077
1      | 1      | 2018-03-08 14:18:27.077

I wish to know how many dates were created for Palm 1 and I also wish to know how many users have created those dates for Palm 1. So the outcome for first is 4 and outcome for second is 3

I am wondering if I can do that in a single query as oppose to having to do a subquery and a join on itself as in example below.

SELECT MT.[PalmId], COUNT(*) AS TotalDates, T1.[TotalUsers]
FROM [MyTable] MT
    LEFT OUTER JOIN (
        SELECT MT2.[PalmId], COUNT(*) AS TotalUsers
        FROM [MyTable] MT2
        GROUP BY MT2.[UserId]
    ) T1 ON T1.[PalmId] = MT.[PalmId]
GROUP BY MT.[PalmId], T1.[TotalUsers]

Upvotes: 0

Views: 124

Answers (2)

DDS
DDS

Reputation: 2478

According to first table you could do something like this:

select count(distinct uerid) as N_Users, 
       count(created_date) as created_date, -- if you use count(*) you consider also rows with 'NULL'
       palmid
from your_table
group by palmid

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270583

If you want "4" and "3", then I think you want:

SELECT MT.PalmId, COUNT(*) AS NumRows, COUNT(DISTINCT mt.UserId) as NumUsers
FROM MyTable MT
GROUP BY MT.PalmId

Upvotes: 0

Related Questions