14

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.

8
select  industry_id
,       sum(case when current_date <= date then clicks end) as today 
,       sum(case when current_date-1 <= date and
                      date < current_date then clicks end) as yesterday
,       sum(case when current_date-4 <= date and 
                      date < current_date-1 then clicks end) as last3days
from    phone_clicks
group by
        industry_id

See it in your SQLFiddle.

7

In 9.4 version, we'll be able to use the FILTER clause:

select  
    t.industry_id,
    sum(t.clicks) filter (where t.date = current_date) 
        as today,
    sum(t.clicks) filter (where t.date = current_date - interval '1 day')
        as yesterday,
    sum(t.clicks) filter (where t.date >= current_date - interval '2 days'
                            and t.date <= current_date) 
        as last_3_days
from 
    phone_clicks as t
group by 
    t.industry_id ;

Tried in SQLfiddle.

  • Looks much better, filter is great syntactic sugar! The query assumes date is a column of type date and not timestamp. That assumption has burned me at times – Andomar Mar 18 '15 at 19:49
  • 1
    @Andomar, yes, I usually have open-closed ranges in date/datetime/timestamp queries. More difficult to be bitten by changes in data types. – ypercubeᵀᴹ Mar 19 '15 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.