CODEWITHSUNDEEP
mysqlselectgroup-byunion
joes_nator
joes_nator

Reputation: 361

Group count results by date between two tables in mysql

I have 2 different tables, users and votes. I would like to get a query to count total users and total votes (not sum) by date. users

id | created_at

votes

id | created_at

I want the result to look like this:

date       | total users | total votes
2012-06-01 | 50          | 90
2012-06-01 | 23          | 0*
2012-06-01 | 80          | 12

*It should take into account that on some days no votes were made

I know how to make the 2 separate queries 

SELECT DATE(created_at), count(id)
FROM vote
GROUP by DATE(created_at)
ORDER by DATE(created_at) DESC

but I don't know how to join them.

Upvotes: 2

Views: 1278

Answers (3)

Rahul Bisht
Rahul Bisht

Reputation: 492

SELECT DATE(created_at),SUM(usercolumnname) as totalusers,
SUM(//votescolumnname) as totalvotes from users u,votes v
where u.date=v.date
GROUP BY u.DATE(created_at)
ORDER BY u.DATE(created_at) DESC

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT created_at, SUM(votes) AS 'TotalVotes', SUM(users) AS 'TotalUsers'
FROM(SELECT DATE(created_at) created_at, COUNT(ID) votes, 0 users 
      FROM votes GROUP BY DATE(created_at)
     UNION 
     SELECT DATE(created_at) created_at, 0 votes, COUNT(ID) users 
     FROM Users GROUP BY DATE(created_at)) t
GROUP BY created_at
ORDER BY created_at DESC

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try this:

SELECT
  DATE(created_at),
  SUM(CASE WHEN `Type` = 'Votes' THEN 1 ELSE 0 END) AS 'TotalVotes',
  SUM(CASE WHEN `Type` = 'Users' THEN 1 ELSE 0 END) AS 'TotalUsers'
FROM
(
    SELECT created_at, 'Votes' `Type` FROM vote
    UNION ALL
    SELECT created_at, 'Users'        FROM Users
) t
GROUP by DATE(created_at)
ORDER by DATE(created_at) DESC

Upvotes: 1

Related Questions