CODEWITHSUNDEEP
mysqljoingroup-by
JoTaRo
JoTaRo

Reputation: 173

mysql combine 2 table with a common group by column

i've 2 tables with date, in timestamp format, i have to count how many record there are every week in total...

...and have the result in just one query...

so i'll explain it better with examples:

2 table, the first one:

table name: visitor

ID  | time  | ref     (now i put in fake timestamp in time column sorry)
--------------------
1   | 3455  | john
2   | 3566  | ted     (week 40)
3   | 8353  | ted     (week 38)
4   | 6673  | katy
5   | 6365  | ted     (week 40)
6   | 4444  | john
7   | 3555  | ted     (week 40)

and the second one (very similar):

table name: users

ID  | time  | ref     (now i put in fake timestamp in time column sorry)
--------------------
1   | 3455  | ted     (week 41)
2   | 3566  | ted     (week 42)
3   | 8353  | ted     (week 40)
4   | 6673  | katy
5   | 6365  | ted     (week 41)
6   | 4444  | john
7   | 3555  | ted     (week 38)
8   | 6789  | ted     (week 43)

i do this query and i obtain this result:

SELECT WEEK(FROM_UNIXTIME(time)) AS week, COUNT( * ) AS tot
FROM visitor WHERE ref='ted' GROUP BY week

table result #1

week | tot
----------
38   |  1
40   |  3
43   |  1

the i do the some for the second table:

SELECT WEEK(FROM_UNIXTIME(time)) AS week, COUNT( * ) AS totuser
FROM users WHERE ref='ted' GROUP BY week

table result #2

week | totuser
----------
38   |  1
40   |  1
41   |  2
42   |  1

but i want with just one query do this result:

week | tot | totusers
----------------------  (when both are 0 doent appear -like 39 for example-)
38   |  1  |   1
40   |  3  |   1
41   |  0  |   2
42   |  0  |   1
43   |  1  |   0

i know that i've to use LEFT JOIN, GROUP BY and IFNULL but i'm doing always something wrong and i cant figure it out.

order by WEEK desc

thank u for any help.

Upvotes: 0

Views: 946

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Technically, what you want is a full outer join, but MySQL does not support that. I approach this using union all and group by:

SELECT weeek, SUM(visitors) as visitors, SUM(users) as users
FROM ((SELECT WEEK(FROM_UNIXTIME(time)) AS week, COUNT( * ) AS visitors, 0 as users
       FROM visitor
       WHERE ref='ted'
       GROUP BY week
      ) UNION ALL
      (SELECT WEEK(FROM_UNIXTIME(time)) AS week, 0, COUNT( * )
       FROM users
       WHERE ref ='ted'
       GROUP BY week
      )
     ) w
GROUP BY week
ORDER BY week;

Note: As in your data, this will only include weeks that have either visitors or users. If you want weeks with neither, it is best to start with a table containing all the weeks you want (some sort of calendar table).

Upvotes: 2

Related Questions