Select count of total records and also distinct records

Question

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]

You're looking for `COUNT(DISTINCT UserId)`. See [here](https://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct) or the docs [here](https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql?view=sql-server-2017). — Adam, Jan 24 '19 at 16:10
In your sample data there are 4 distinct user ids and 1 distinct date. Can you explain 4 and 3? — forpas, Jan 24 '19 at 16:10
@forpas 4 is meant to be one, it slipped by me. Thanks for noticing, i updated the question. — Bagzli, Jan 24 '19 at 16:12

DDS · Accepted Answer · 2019-01-24T16:41:41.847

2

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

edited Jan 24 '19 at 16:41

answered Jan 24 '19 at 16:10

DDS

2,340
16
34

@Bojan . . . It is curious that you accepted this answer because it does not return the results that you specify in the question. – Gordon Linoff Jan 24 '19 at 16:29
@GordonLinoff I accepted because it listed the clue, DISTINCT. All I did was remove the subquery and added count(distinct userId) – Bagzli Jan 24 '19 at 16:32
DDS you should probably just change your answer to match what I asked for. Just change `count(distinct created_date) as created_date` to `count(*)` – Bagzli Jan 24 '19 at 16:32
@bojan Thanks, I did it, but I prefer `count (created_date)` as it doesn't count nulls – DDS Jan 24 '19 at 16:36

score 0 · Answer 2 · answered Jan 24 '19 at 16:28

0

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

answered Jan 24 '19 at 16:28

Gordon Linoff

1,242,037
58
646
786

Select count of total records and also distinct records

2 Answers2