CODEWITHSUNDEEP
sqlpostgresql
Adesso
Adesso

Reputation: 977

Aggregating postgres union results

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.

Sample Data Columns for Table content: id,creationdate,contenttype

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

If Use this SQL query:

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;

I get the result

|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|

What I want is:

|create_date|PAGE|ATTACHMENT|
|2016-04-02|1|2|
|2016-04-03|2|2|
|2016-04-04|3|0|

Upvotes: 1

Views: 547

Answers (3)

sagi
sagi

Reputation: 40481

You can do it with conditional aggregation by selecting from the table only once:

SELECT date(creationdate) AS create_date,
       count(CASE WHEN contenttype='PAGE' then 1 end) as PAGE,
       count(CASE WHEN contenttype='ATTACHMENT' then 1 end) as ATTACHMENT
FROM 
  content 
GROUP BY content.creationdate 
ORDER BY create_date ASC;

Upvotes: 2

jurhas
jurhas

Reputation: 653

several ways:

SELECT   date(c.creationdate) AS create_date,
p p_count,a_count 
FROM 
(SELECT DISTINCT creationdate FROM content) c INNER JOIN (SELECT  creationdate,count() p_count FROM CONTENT GROUP
BY creationdate,contenttype   HAVING contenttype='PAGE') p on p.creationdate=c.creationdate
INNER JOIN (SELECT  creationdate,count() a_count FROM CONTENT GROUP
BY creationdate,contenttype   HAVING contenttype='ATTACHMENT') p
ON p.creationdate=c.creationdate ORDER BY c.creationdate

one is this

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

You need a conditional aggregate:

SELECT 
  date(creationdate) AS create_date,
  SUM(CASE WHEN contenttype='PAGE' THEN 1 ELSE 0 END) AS PAGE,
  SUM(CASE WHEN contenttype='ATTACHMENT' THEN 1 ELSE 0 END) AS ATTACHMENT
FROM 
  content 
GROUP BY content.creationdate 
ORDER BY create_date ASC;

Upvotes: 1

Related Questions