'SQL Sum over several columns of an entry with ad hoc created columns

I have the following table, like:

woker date amount
jeff 04-04-2022 4.00
jeff 04-05-2022 2.00
jeff 04-08-2022 3.50
dave 04-04-2022 1.00
dave 04-07-2022 6.50

It contains the date and the amount of hours worked by a worker.

Now I want to create a table like the following with a select to show hours per weekday. The column "count" should represent the amount of days where the worker has hours. The column "sum" should sum the hours for this week. So the final result should be like this:

worker mon tue wed thu fri sat sun count sum
jeff 4.00 2.00 null null 3.50 null null 3 9.50
dave 1.00 null null 6.50 null null null 2 7.50

My statement so far is:

SELECT worker,
CASE WHEN DATEPART(weekday,date) = 1 THEN amount END AS mon,
CASE WHEN DATEPART(weekday,date) = 2 THEN amount END AS tue,
CASE WHEN DATEPART(weekday,date) = 3 THEN amount END AS wed,
CASE WHEN DATEPART(weekday,date) = 4 THEN amount END AS thu,
CASE WHEN DATEPART(weekday,date) = 5 THEN amount END AS fri,
CASE WHEN DATEPART(weekday,date) = 6 THEN amount END AS sat,
CASE WHEN DATEPART(weekday,date) = 7 THEN amount END AS sun
FROM table

So now I need help to get the last two columns. Can anybody explain, how to sum up / count values over multiple columns withi one entry?

Thank you.

sqlsql-serverdatabasetsql


Solution 1:[1]

We use the function SUM so as to be able to agregate the different lines. We add the week number in the SELECT and the GROUP BY so as to separate the weeks and know which week of the year we are looking at. We could also add the year if the same table will be used for long enough.

SET DATEFIRST 1;
SELECT 
  DATEPART(week, date) AS "week",
  worker,
  CASE WHEN DATEPART(weekday,date) = 1 THEN amount END ) AS mon,
  SUM( CASE WHEN DATEPART(weekday,date) = 2 THEN amount END ) AS tue,
  SUM( CASE WHEN DATEPART(weekday,date) = 3 THEN amount END ) AS wed,
  SUM( CASE WHEN DATEPART(weekday,date) = 4 THEN amount END ) AS thu,
  SUM( CASE WHEN DATEPART(weekday,date) = 5 THEN amount END ) AS fri,
  SUM( CASE WHEN DATEPART(weekday,date) = 6 THEN amount END ) AS sat,
  SUM( CASE WHEN DATEPART(weekday,date) = 7 THEN amount END ) AS sun,
  COUNT(DISTINCT DATEPART(weekday,date) ) AS "count",
amount AS "sum"
FROM table
GROUP BY 
  DATEPART(week, date),
  worker
ORDER BY 
  DATEPART(week, date),
  worker;

Solution 2:[2]

Conditional aggregation is an option (don't forget to set the first day of the week using SET DATEFIRST):

SET DATEFIRST 1

SELECT 
   worker,
   SUM(CASE WHEN DATEPART(weekday, date) = 1 THEN amount END) AS mon,
   SUM(CASE WHEN DATEPART(weekday, date) = 2 THEN amount END) AS tue,
   SUM(CASE WHEN DATEPART(weekday, date) = 3 THEN amount END) AS wed,
   SUM(CASE WHEN DATEPART(weekday, date) = 4 THEN amount END) AS thu,
   SUM(CASE WHEN DATEPART(weekday, date) = 5 THEN amount END) AS fri,
   SUM(CASE WHEN DATEPART(weekday, date) = 6 THEN amount END) AS sat,
   SUM(CASE WHEN DATEPART(weekday, date) = 7 THEN amount END) AS sun,
   COUNT(DISTINCT DATEPART(weekday, date)) AS [count],
   SUM(amount) AS [sum]
FROM (VALUES
   ('jeff', CONVERT(date, '20220404'), 4.00),
   ('jeff', CONVERT(date, '20220405'), 2.00),
   ('jeff', CONVERT(date, '20220408'), 3.50),
   ('dave', CONVERT(date, '20220404'), 1.00),
   ('dave', CONVERT(date, '20220407'), 6.50)
) t (worker, date, amount)
GROUP BY worker
ORDER BY worker

Result:

worker mon tue wed thu fri sat sun count sum
dave 1.00 6.50 2 7.50
jeff 4.00 2.00 3.50 3 9.50

If you want to summarize the input data based on time interval, you need a different statement:

SET DATEFIRST 1

SELECT 
   worker,
   --DATEPART(week, date) AS week,
   DATEPART(month, date) AS month,
   SUM(CASE WHEN DATEPART(weekday, date) = 1 THEN amount END) AS mon,
   SUM(CASE WHEN DATEPART(weekday, date) = 2 THEN amount END) AS tue,
   SUM(CASE WHEN DATEPART(weekday, date) = 3 THEN amount END) AS wed,
   SUM(CASE WHEN DATEPART(weekday, date) = 4 THEN amount END) AS thu,
   SUM(CASE WHEN DATEPART(weekday, date) = 5 THEN amount END) AS fri,
   SUM(CASE WHEN DATEPART(weekday, date) = 6 THEN amount END) AS sat,
   SUM(CASE WHEN DATEPART(weekday, date) = 7 THEN amount END) AS sun,
   COUNT(DISTINCT DATEPART(weekday, date)) AS [count],
   SUM(amount) AS [sum]
FROM (VALUES
   ('jeff', CONVERT(date, '20220404'), 4.00),
   ('jeff', CONVERT(date, '20220405'), 2.00),
   ('jeff', CONVERT(date, '20220408'), 3.50),
   ('dave', CONVERT(date, '20220404'), 1.00),
   ('dave', CONVERT(date, '20220407'), 6.50)
) t (worker, date, amount)
--GROUP BY worker, DATEPART(week, date)
--ORDER BY worker, DATEPART(week, date)
GROUP BY worker, DATEPART(month, date)
ORDER BY worker, DATEPART(month, date)

Solution 3:[3]

Instead om doing alot aggregates, cases and dateparts I would do a pivot table out of it like below. (Note that my days-weekdays ref may not be the same as yours)

Test data:

declare @tbl table (worker varchar(20), [date] date, amount dec(5,2))
INSERT INTO @tbl
SELECT              'jeff', '04-04-2022', 4.00
UNION SELECT    'jeff', '05-04-2022', 2.00
UNION SELECT    'jeff', '08-04-2022', 3.50
UNION SELECT    'dave', '04-04-2022', 1.00
UNION SELECT    'dave', '07-04-2022', 6.50

The actual code:

SELECT 
        worker
    , [1] as sun
    , [2] as mon
    , [3] as tue
    , [4] as wed
    , [5] as thu
    , [6] as fri
    , [7] as sat
    , [count]
    , [sum]
FROM (select 
            worker
        , amount
        , datepart(weekday, t.[date]) dp
        , COUNT(amount) OVER( partition by worker) as [count]
        , SUM(amount) OVER( partition by worker) as [sum]
    from @tbl t
) p
PIVOT(
    SUM(amount)
    FOR dp in ([1], [2], [3], [4], [5], [6], [7])
) piv

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Soerman

Related Questions