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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Jim HornMicrosoft 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Olaf DoschkeSoftware 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:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.