CODEWITHSUNDEEP
mysql
yoyoma
yoyoma

Reputation: 3536

Mysql group by first table date and count distinct field from second table

I have users table

id | email           | website
1    [email protected]     google.com
2    [email protected]   google.com
3    [email protected]   yahoo.com
4    [email protected]     yahoo.com

And a groups table

id | group     | user_id | created
1    group1       1         2018-06-06
2    group2       2         2018-06-06
3    group3       3         2018-09-09
4    group4       4         2018-06-06

I need to get a count of each website for each created day, so the above would give me 2 for google.com on 2018-06-06

Ie, 

website   | count   | date

google.com    2        2018-06-06
yahoo.com     1        2018-06-06
yahoo.com     1        2018-09-09

I have the rows coming back grouped by date, but the count is all 1 for each row using the following query

select count(distinct users.website), groups.created 
from user_groups as groups
 join users on users.id = groups.user_id
 group by groups.created

So i'm getting simply

count(distinct users.website)  |   created
 1                                  2018-06-06
 1                                  2018-06-06
 1                                  2018-06-06
 1                                  2018-09-09

Upvotes: 0

Views: 42

Answers (2)

Fahmi
Fahmi

Reputation: 37473

Try below

DEMO

select website,created,count(website) as cnt
from groups t2 join users t1 on t1.id = t2.userid
group by website, created

Upvotes: 1

Shalvin
Shalvin

Reputation: 178

Try

Select distinct(groups.created),count(distinct(users.website))
from user_groups as groups
join users on users.id = groups.user_id
group by groups.created

Should work.

Upvotes: 0

Related Questions