Tyler_1 Tyler_1 - 1 month ago 15
MySQL Question

MySQL - Get two counts from two tables and group them by another column

[Edit] It appears that my query is mostly fine as is - but there is something missing that causes certain side IDs to get counted in a loop or something. I suspect the issue is with the double LEFT JOINs but I'm having trouble figuring it out.

I have three tables (

Pre
,
Post
,
Schedule
), I want to get a count of the rows from table
Pre
and table
Post
and have the result group both counts as different columns based on the site ID (which is common across all three table) if the install date for that side ID matches my query. Here's what I'm working with:

Table 'Pre'
site | timestamp
-------------------------
0101 | 2016-01-01 13:00
0101 | 2016-01-01 12:00
3345 | 2016-01-01 12:30
6500 | 2016-01-01 16:00
6500 | 2016-01-01 11:00
0101 | 2016-01-01 18:00

Table 'Post'
site | timestamp
-------------------------
0101 | 2016-01-02 03:00
0101 | 2016-01-02 02:00
6500 | 2016-01-02 01:00
0101 | 2016-01-02 08:00
0101 | 2016-01-02 05:30
8888 | 2016-12-31 12:30

Table 'Schedule'
site | install_date
------------------------
0101 | 2016-01-01
3345 | 2016-01-01
6500 | 2016-01-01
8888 | 2015-12-29

Desired Query Result:
site | install_date | pre | post
-------------------------------------
0101 | 2016-01-01 | 3 | 4
3345 | 2016-01-01 | 1 | 0
6986 | 2016-01-01 | 2 | 1


And here's the query I've tried but I'm getting inaccurate results.

SELECT t1.`site`, t1.`install_date`, SUM(CASE WHEN t2.`timestamp` >= NOW() - INTERVAL 1 DAY THEN 1 ELSE 0 END) as 'pre',
SUM(CASE WHEN t3.`timestamp` >= NOW() - INTERVAL 1 DAY THEN 1 ELSE 0 END) as 'post'
FROM `Schedule` t1
LEFT JOIN `Pre` t2 ON t1.`site` = t2.`site`
LEFT JOIN `Post` t3 ON t1.`site` = t3.`site`
WHERE t1.`install_date` = CURDATE() - INTERVAL 1 DAY
GROUP BY t1.`site`

Answer

I had some help from a friend:

Joining the ‘pre’ and ‘post’ tables together results in a cartesian product - so the three ‘0101’ rows in the ‘pre’ table will join with all 4 ‘0101’ rows in the ‘post’ table resulting in a sum of 12, not 3 as you want. To get individual counts from two different tables, you need to use sub-select queries, like:

SELECT site, install_date, (SELECTION COUNT(*) FROM pre WHERE Pre.site = Schedule.site AND Pre.timestamp <= NOW() - INTERVAL 1 DAY) AS 'pre', (SELECT COUNT(*) FROM post WHERE Post.site = Schedule.site AND Post.timestamp >= NOW() - INTERVAL 1 DAY) AS 'post' FROM Schedule