MySQL Double count on left join

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)

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.

Answer 1

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