MySQL Double count on left join

count, left-join, mysql, sql

Solution

Count the installs and the uninstall separately, adding a column (of zeroes) for the other count to each of them. Then combine the two with `UNION`, group by date once again and take the max for each date (to eliminate the added zeroes):

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

Problem

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) ``` The Query ("Me vs. The 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) ``` The "Expected" Output ``` DATE(installs.created) | COUNT(installs.id) | COUNT(uninstalls.id) 2012-11-20 | *installs on date* | *uninstalls on date* ``` So - I am looking a row per day, with the number of installs/uninstalls that happened on that day. The Problem The data for the 'installs' is correct for each day. BUT the data for the 'uninstalls' for each day is sadly incorrect.

Original source