MySQL Double count left

Question

Database structure

CREATE TABLE installs( id INT, PRIMARY KEY(id), created DATETIME) CREATE TABLE uninstalls( id INT, PRIMARY KEY(id), created DATETIME, install_id INT) 

Request ("I versus MySQL")

 SELECT DATE(installs.created), COUNT(installs.id), COUNT(uninstall.id) FROM installs LEFT JOIN uninstalls ON uninstalls.install_id = installs.id GROUP BY DATE(installs.created) 

"Expected" exit

 DATE(installs.created) | COUNT(installs.id) | COUNT(uninstalls.id) 2012-11-20 | *installs on date* | *uninstalls on date* 

So - I look at the line per day with the number of installations / deletes that occurred on that day.

Problem

The data for "installs" is correct for each day. BUT the data for "uninstalls" for each day is sadly wrong.

Answer 1

Count installs and deletions separately by adding a column (zeros) for a different account for each one. Then combine the two with UNION , group by date and take max for each date (to exclude added zeros):

 SELECT created as date, max(installs) as installs, max(uninstalls) as uninstalls FROM (SELECT created, count(*) AS installs, 0 AS uninstalls FROM installs GROUP BY created UNION ALL SELECT created, 0 AS installs, count(*) AS uninstalls FROM uninstalls GROUP BY created) c GROUP BY created ORDER BY created 

Answer 2

This problem occurs because you bind the installation and removes it. That is, you show the settings for a given date and are deleted for these settings, and not on the installation date.

If you want to see the amount of each event on a given date, you will need to join the date, not the installation ID. If this is a large data set, it will be very slow (the optimizer cannot use indexes in a field when grouping or combining based on a function in this field).

 SELECT DATE(installs.created), COUNT(installs.id), COUNT(uninstall.id) FROM installs LEFT JOIN uninstalls ON DATE(uninstalls.created) = DATE(installs.created) GROUP BY DATE(installs.created) 

Answer 3

Create a CTE that includes all dates for installation and uninstallation, then join the installation and uninstallation of the table separately.

 ; WITH Dates AS ( SELECT DATE(created) AS created FROM ( SELECT created FROM installs UNION ALL SELECT created FROM uninstalls ) a GROUP BY DATE(created) ) , InstallCTE AS ( SELECT DATE(created) AS created , COUNT(*) AS c FROM installs GROUP BY DATE(created) ) , UninstallCTE AS ( SELECT DATE(created) AS created , COUNT(*) AS c FROM uninstalls GROUP BY DATE(created) ) SELECT d.created AS [date] , ic AS install_count , uc AS uninstall_count FROM Dates d LEFT JOIN InstallCTE i ON d.created = i.created LEFT JOIN UninstallCTE u ON d.created = u.created