Let's say I have a dataset like:
DATE PAGE_ID HITS
2014-1-1 1 100
2014-1-1 2 50
2014-1-1 3 20
2014-1-2 1 20
2014-1-2 2 40
2014-1-2 3 20
I can currently use:
SELECT date, hits
FROM my_table
WHERE PAGE_ID = 1
GROUP BY date
ORDER BY date ASC
To get daily stats for 1 page_id.
But how can I get a date aggregate for the pages, but in different columns? Like this:
DATE Page_1_hits All_other_pages
2014-1-1 100 70
2014-1-2 20 60
For the current Postgres version (9.4)
select date,
sum(hits) filter (where page_id = 1) as page_1_hits,
sum(hits) filter (where page_id <> 1) as all_other_pages
from my_table
group by date
order by date asc;
For earlier versions you need to use a case
select date,
sum(case when page_id = 1 then hits end) as page_1_hits,
sum(case when page_id <> 1 then hits end) as all_other_pages
from my_table
group by date
order by date asc;