Count data using multiple date ranges
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.
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.