Probably this has been asked before, but I can't figure it out.
I have a phone_clicks
table (sql fiddle http://sqlfiddle.com/#!15/855e0/1)
CREATE TABLE phone_clicks (
id integer NOT NULL,
date date NOT NULL,
industry_id integer NOT NULL,
clicks integer DEFAULT 0 NOT NULL
);
insert into phone_clicks(id, date, industry_id, clicks)
values
(1, '2015-03-16', 1, 15),
(2, '2015-03-16', 2, 7),
(3, '2015-03-16', 3, 0),
(4, '2015-03-17', 1, 12),
(5, '2015-03-17', 3, 4),
(6, '2015-03-17', 4, 22),
(7, '2015-03-18', 1, 19),
(8, '2015-03-18', 2, 35);
This table holds phone click event counts for multiple industry_ids and dates.
Is it possible to count these clicks for all available industry_ids with multiple date ranges as conditions?
I would like to have this output:
------------------------------------------------
industry_id | today | yesterday | last 3 days |
------------------------------------------------
1 | 19 | 12 | 46 |
------------------------------------------------
2 | 35 | 0 | 42 |
------------------------------------------------
3 | 0 | 4 | 4 |
------------------------------------------------
4 | 0 | 22 | 22 |
------------------------------------------------
I've tried using counting with partition by date, but got nowhere.
Is it possible to select this data in one query?
Additional benefit would be to be able to specify previous months as date ranges: today, yesterday, March, February, January, etc
UPDATE: I've updated the fiddle to define current month, previous months and pre-previous month as the date ranges.
SQL Fiddle: http://sqlfiddle.com/#!15/855e0/46
I'm using PostgreSQL 9.3, but 9.4 solutions are welcome, because we'll be migrating to it soon.
Best Answer
See it in your SQLFiddle.