current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite

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.

share|improve this question
1  
+1 for providing a fiddle to work with. Wish others would –  Phil Mar 18 at 15:50
1  
@Phil: And it has a proper table definition, too. Sadly, the question doesn't bother to provide the version of Postgres in use. Every programmer should know to do that with a question like that. So I can't vote for it. Also, OP announces updates in the Fiddle, but didn't update the link. This needs some cleaning up. –  Erwin Brandstetter Mar 19 at 23:25
    
@ErwinBrandstetter, thanks, updated the question. –  Valentin Vasilyev Mar 20 at 8:02

2 Answers 2

up vote 3 down vote accepted 
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.

share|improve this answer
    
using case inside an aggregate is something I never saw before, thank you, @Andomar! However, the query isn't returning the correct results, please see the fiddle –  Valentin Vasilyev Mar 18 at 15:52
    
please update your answer, instead of 1s, use clicks column, the answer is correct overall, thanks! –  Valentin Vasilyev Mar 18 at 15:58
1  
You need so sum clicks not 1 –  a_horse_with_no_name Mar 18 at 15:59
    
@a_horse_with_no_name: Updated to use clicks, though Valentin seems to have figured that out by himself :) –  Andomar Mar 18 at 19:50
up vote 4 down vote

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 ;
share|improve this answer
    
No 9.4 fiddle unfortunately :( –  ypercube Mar 18 at 16:09
    
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 at 19:49
    
@Andomar: the question has the CREATE TABLE script. –  ypercube Mar 18 at 20:31
    
Right, in this case it is. But when you read this query in a year or so, you'll have to double-check the type of the date column –  Andomar Mar 19 at 17:11
    
@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 at 17:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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