Aggregating postgres union results
<p>I am trying to get a singel result row per date in SQL, using a single table in a postgres DB. I tried using Union, but I think this is not the right way. Can somone help me construct the right SQL.</p>

<p>Sample Data Columns for Table content: id,creationdate,contenttype</p>

<pre><code>1 |2016-04-02|PAGE
2 |2016-04-02|ATTACHMENT
3 |2016-04-02|PAGE
4 |2016-04-03|ATTACHMENT
5 |2016-04-03|PAGE
6 |2016-04-03|ATTACHMENT
7 |2016-04-03|PAGE
8 |2016-04-04|ATTACHMENT
9 |2016-04-04|ATTACHMENT
10|2016-04-04|ATTACHMENT
</code></pre>

<p>If Use this SQL query:</p>

<pre><code>SELECT 
  date(creationdate) AS create_date,
  COUNT(*) AS PAGE,0 AS ATTACHMENT 
FROM 
  content 
WHERE 
  contenttype='PAGE' GROUP BY content.creationdate 
UNION
SELECT 
  date(creationdate) AS create_date,
  0 AS PAGE,COUNT(*) AS ATTACHMENT
FROM 
  content 
WHERE 
  contenttype='ATTACHMENT' GROUP BY content.creationdate 
ORDER BY create_date ASC;
</code></pre>

<p>I get the result</p>

<pre><code>|create_date|PAGE|ATTACHMENT|
|2016-04-02|0|2|
|2016-04-02|1|0|
|2016-04-03|0|2|
|2016-04-03|1|0|
|2016-04-04|3|0|
</code></pre>

<p>What I want is:</p>

<pre><code>|create_date|PAGE|ATTACHMENT|
|2016-04-02|1|2|
|2016-04-03|2|2|
|2016-04-04|3|0|
</code></pre>