getting sum of grouped columns in sql server

Hi experts,

This is a followup to this ticket.

My question is on the same table used in that ticket.

So one of the solutions mentioned in that ticket was this. Which worked fine. It grouped employees by EmployeeID

select case when rn = 1 then EmployeeID else '' end as EmployeeID
      ,[Region], [Company], [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff] 
from   ( select [Region], [Company], cast(employeeid as varchar(10)) as EmployeeID, [LastName], [FirstName], [MidInit], [HoursWorked], [TookTimeOff]
               ,row_number() over (partition by employeeid order by region,company,LastName,FirstName,HoursWorked) as rn
         from TestEmployees2) as SDT
order by [LastName],[FirstName], RN

Open in new window

Now how do I add a total column for each employeeid that sums the total for the HoursWorked for each EmployeeID?
So my desired result should look like this drawing i sketched in excel. I basically want to add the Employee Total rows colored blue in this image.

Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Doing this is not considered best practice as a set of data should have a single schema and not 'line specific' differing schemas such as subtotals.  How would your reporting layer differentiatie between subtotal lines and detail lines?  How would a grand total line not double-count as it also counts subtotals?

It's not that we can't answer this question, its that based on past history we choose that database return sets are optimized for speed, whereas reporting layers are optimized for cosmetic appearance, and it's better to use the strengths of each.

WITH ROLLUP may have a few options for you, but again I'm not recommending this approach.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Now how do I add a total column for each employeeid that sums the total for the HoursWorked for each EmployeeID?
I reeeeeeeeeeeeeeeally recommend doing this in your reporting application using some kind of GROUP BY on Employee, and not in the database layer by adding subtotals to a return set.

So .. what are you using for reporting?
maqskywalkerAuthor Commented:
I'm trying to learn how do such a total in a sql query.

I can do it in my reporting tool like crystal reports or ssrs but I'm trying to learn how do get such a total with t-sql.
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
Just notice how many Es Jim used  in his first posting.

The layout you show really does not qualify for a SQL solution. SQL results have in common with tables to be tuples of an exact same structure in each row. So there are no rows with a colspan of 2 for one total row. Overall total rows are an exception, WITH ROLLUP is one option, as Jim already stated.

If your main goal really is to get a deeper level understanding of SQL, real case problems are a nice exercise, but you should consider looking the other way around, look at what either ANSI SQL or T-SQL constructs you don't yet know and read about cases they are used for.

Bye, Olaf.
maqskywalkerAuthor Commented:
All Courses

From novice to tech pro — start learning today.