SQL - SUMIF substitute?-CodePudding

This question is best asked using an example - if I have daily data (in this case, daily Domestic Box Office for the movie Elvis), how can I sum only the weekend values?

If the data looks like this:

Date	DBO
6/24/2022	12755467
6/25/2022	9929779
6/26/2022	8526333
6/27/2022	4253038
6/28/2022	5267391
6/29/2022	4010762
6/30/2022	3577241
7/1/2022	5320812
7/2/2022	6841224
7/3/2022	6290576
7/4/2022	4248679
7/5/2022	3639110
7/6/2022	3002182
7/7/2022	2460108
7/8/2022	3326066
7/9/2022	4324040
7/10/2022	3530965

I'd like to be able to get results that look like this:

Weekend	DBO Sum
1	31211579
2	18452612
3	11181071

Also - not sure how tricky this would be but would love to include percent change v. last weekend.

Weekend	DBO Sum	% Change
1	31211579
2	18452612	-41%
3	11181071	-39%

I tried this with CASE WHEN but I got the results in different columns, which was not what I was looking for.

SELECT 
  ,SUM(CASE 
    WHEN DATE BETWEEN '2022-06-24' AND '2022-06-26' THEN index 
    ELSE 0
    END) AS Weekend1
 ,SUM(CASE 
    WHEN DATE BETWEEN '2022-07-01' AND '2022-07-03' THEN index 
    ELSE 0
    END) AS Weekend2
  ,SUM(CASE 
    WHEN DATE BETWEEN '2022-07-08' AND '2022-07-10' THEN index 
    ELSE 0
    END) AS Weekend3
FROM Elvis

CodePudding user response：

I would start by filtering the data on week-end days only. Then we can group by week to get the index sum ; the last step is to use window functions to compare each week-end with the previous one:

select iso_week,
    row_number() over(order by iso_week) weekend_number,
    sum(index) as dbo_sum,
    ( sum(index) - lag(sum(index) over(order by iso_week) )
        / nullif(lag(sum(index)) over(order by iso_week), 0) as ratio_change
from (
    select e.*, extract(isoweek from date) iso_week
    from elvis e
    where extract(dayofweek from date) in (1, 7)
) e
group by iso_week
order by iso_week

CodePudding user response：

Consider below

select *, 
  round(100 * safe_divide(dbo_sum - lag(dbo_sum) over(order by week), lag(dbo_sum) over(order by week)), 2) change_percent
from (
  select extract(week from date   2) week, sum(dbo) dbo_sum
  from your_table
  where extract(dayofweek from date   2) in (1, 2, 3)
  group by week
)

if applied to sample data in your question - output is