CODEWITHSUNDEEP
sqlselect
user3861284
user3861284

Reputation: 249

SELECT statement for getting counts in table

I have a table "downloads" with the following information:

objKey     CustNum    UserId    DownloadDate
51048879   123        7654       2014-09-01
51048879   234        32434      2014-09-02
51048879   123        7654       2014-09-01
51047379   2132       3456       2014-08-20
51048879   2132       9786       2014-09-01
51047379   123        7654       2014-09-02

I want to get counts by downloadDate of how many products, customers, and users downloaded. So I would want:

DownloadDate      Products       Customers    Users
2014-09-01         1              2            2
2014-09-02         2              2            2
2014-08-20         1              1            1

Can someone help me with the SELECT statement? Each time I have tried I get incorrect numbers. I would think this should be easier than what I tried.

Upvotes: 0

Views: 59

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You want count(distinct). I think this does what you want:

select DownloadDate, count(distinct objKey) as Products,
       count(distinct CustNum) as Customers,
       count(distinct UserId) as Users
from table t
group by DownloadDate
order by DownloadDate;

Upvotes: 5

duffymo
duffymo

Reputation: 308743

Something like this might do it:

select DownloadDate, count(disinct objKey) as products, count(distinct CustNum) as customers, count(distinct UserId) as users
from downloads
group by DownloadDate
order by DownloadDate

Upvotes: 0

juergen d
juergen d

Reputation: 204746

select downloaddate,
       count(distinct objkey) as products,
       count(distinct custnum) as customers,
       count(distinct userid) as users
from downloads
group by downloaddate

Upvotes: 5

Related Questions