CODEWITHSUNDEEP
sql-servert-sqlssms
redviper2100
redviper2100

Reputation: 265

Add a Total row after each week

I am searching for a way to sum columns by week. This is the initial table data.

Date            WeekNo      Col1        Col2        Col3
2020/07/01      27           1           4          3
2020/07/04      27           3           3          1
2020/07/06      28           1           1          1
2020/07/11      28           1           3          8

and I want to add a row total to every end of the week like this:

Date            WeekNo      Col1        Col2        Col3
2020/07/01      27           1           4          3
2020/07/04      27           3           3          1
TOTAL           27           4           7          4
2020/07/06      28           1           1          1
2020/07/11      28           1           3          8
TOTAL           28           2           4          9

I tried something similar the the code below but I have multiple columns to sum. Do you have other ideas, suggestions?

Also, grouping sets does not create a new row if there is no data for that week to sum (like 0 or NULL).

SELECT
    YEAR(Date) AS OrderYear,
    MONTH(Date) AS OrderMonth,
    SUM(Col1) AS SumCol1
FROM tb
GROUP BY
    GROUPING SETS
    (
        YEAR(Date), --1st grouping set
        (YEAR(Date),MONTH(Date)) --2nd grouping set
    )

Upvotes: 1

Views: 122

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65373

You only need to add (DATEPART(wk, Date)) as aggregation rule after listing the other non-aggregated columns Date,Col1, Col2, Col3. Btw, you do not need a WeekNo column, since it could already be computer by use of (DATEPART(wk, Date)).

So far so good, but ordering is such a daunting task as returning null values for WeekNo column for subtotal. I used two analytic functions ( ROW_NUMBER() and FISRT_VALUE() to overcome this problem ) :

SELECT COALESCE(Date,'TOTAL') As Date, 
       DATEPART(wk, Date) AS WeekNo,
       SUM(Col1) AS Col1,
       SUM(Col2) AS Col2,
       SUM(Col3) AS Col3                       
FROM tb
GROUP BY GROUPING SETS ( ( Date,Col1, Col2, Col3 ),
                           (DATEPART(wk, Date))        
                         )
ORDER BY CASE WHEN DATEPART(wk, Date) IS NULL 
              THEN
               ROW_NUMBER() OVER (PARTITION BY DATEPART(wk, Date)  
                              ORDER BY COALESCE(Date,'TOTAL') )
              ELSE
               DATEPART(wk, Date) -
               FIRST_VALUE(DATEPART(wk, Date)) 
                    OVER ( ORDER BY COALESCE(Date,'TOTAL') ) + 1               
              END,
         DATEPART(wk, Date)

Demo

Upvotes: 2

Sander
Sander

Reputation: 4042

Is this what you are after?

My solution uses the built in with rollup addition to the group by clause.

-- create sample data
declare @data table
(
    [Date] Date,
    WeekNo int,
    Col1 int,
    Col2 int,
    Col3 int
);

insert into @data ([Date], WeekNo, Col1, Col2, Col3) values
('2020-07-01', 27, 1, 4, 3),
('2020-07-04', 27, 3, 3, 1),
('2020-07-06', 28, 1, 1, 1),
('2020-07-11', 28, 1, 3, 8);

-- solution
select  case when grouping(d.Date) = 0
            then convert(nvarchar(10), d.Date) -- type conversion so all column values have the same type
            else 'TOTAL'
        end as 'Date',
        d.WeekNo,
        sum(d.Col1) as 'Col1',
        sum(d.Col2) as 'Col2',
        sum(d.Col3) as 'Col3'
from @data d
group by d.WeekNo, d.Date with rollup -- "roll up" the aggregations
having grouping(d.WeekNo) = 0; -- filter out aggregation across weeks

Gives me:

Date       WeekNo      Col1        Col2        Col3
---------- ----------- ----------- ----------- -----------
2020-07-01 27          1           4           3
2020-07-04 27          3           3           1
TOTAL      27          4           7           4
2020-07-06 28          1           1           1
2020-07-11 28          1           3           8
TOTAL      28          2           4           9

Upvotes: 3

Related Questions