I have the following table, like:

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:

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.

CodePudding user response：

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:

CodePudding user response：

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;