SQL multi query get data per day

I have two tables with data (Units_raw & Units_raw_andr). I want to get the units of both tables by date. I’m new to combining tables into one query, but tried the following:

SELECT `DateKey` AS 'date', `InstallEvents` AS 'DownloadAndroid' FROM `Units_raw_andr`
UNION 
SELECT `datetime` AS 'date', `units` AS 'DownloadiOS' FROM `Units_raw` GROUP BY 'date';

This gives me the following result:

 [ DATE ]    | [ DownloadAndroid ]
2020-04-04   |       293
2020-04-03   |       210

I want to achieve this:

 [ DATE ]    |   [ DownloadAndroid ]   |   [DownloadiOS]
2020-04-04   |          293            |        301
2020-04-03   |          210            |        202

What am I doing wrong and how can I get this working?

Answer

The operation that makes data sets wider (more columns) is called a JOIN. The operation that makes datasets taller (more rows) is a UNION

As such I think you want:

SELECT COALESCE(i.DateKey, a.DateKey) AS `Date`, a.InstallEvents AS DownloadAndroid, i.DownloadIOS
FROM 
  Units_raw_andr a
  FULL OUTER JOIN
  (
    SELECT `datetime` AS DateKey , units AS DownloadiOS 
    FROM Units_raw
    GROUP BY `datetime`
  ) i
  ON a.DateKey = i.DateKey

Of course, Murphy’s Law will dictate that it’ll turn out you’re using an old version of MySQL that doesn’t support full outer join, in which case you’ll need to do a left outer join union to a right outer join..

You might not be able to change it now, but try really hard NOT to use words that are reserved words in SQL, as column names. Stuff like “Date” and “DateTime” are a) reserved words and b) relatively meangingless – think of more descriptive names that say what the date is, like InstallDate, ShippingDate, PurchaseDate etc