CODEWITHSUNDEEP
sqlsql-servercountsum
germantom
germantom

Reputation: 415

Count the accumulated hours within a column

I have a table which holds information on the type of work a worker does and the amount of hours spent on the work.

eg. 

work_id | user_id | work_type | hours_spent
-------------------------------------------------   
    1   |     1   |  Maintain |     7
    2   |     1   |  sick     |     4  
    3   |     1   |  maintain |     3
    4   |     1   |  maintain |     6
    5   |     2   |  Web      |     5
    6   |     2   |  Develop  |     8
    7   |     2   |  develop  |     5
    8   |     3   |  maintain |     5
    9   |     3   |  sick     |     7
    10  |     3   |  sick     |     7

I would like to count the amount of accumulated hours each user has spent on a type of work to display something like this:

user id | work_type | hours_spent
-----------------------------------
    1   |  maintain |     16 
    1   |  sick     |      4
    2   |  Web      |      5
    2   |  develop  |     13
    3   |  maintain |      5
    3   |  sick     |     14

The sum() function I'm using now returns all the hours in the hours_spent column. Is this the right function for what I want to achieve?

I'm using SQL Server 2008 R2.

Upvotes: 1

Views: 86

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

SELECT 
  user_id, 
  work_type = LOWER(work_type), 
  hours_spent = SUM(hours_spent)
FROM dbo.tablename
GROUP BY user_id, LOWER(work_type)
ORDER BY user_id, LOWER(work_type);
  • You don't need LOWER() there unless you have a case sensitive collation. And if you do, enter those strings consistently - or better yet, use a lookup table for those strings and store a tinyint in the main table instead.

Upvotes: 4

Related Questions