Get total of each columne in sql statement that use SUM

Hi, i wrote an sql statement that get the total for each action and the result is like this

Name      TotalAction1        TotalAction2         Total
--------------------------------------------------------------------
user1      1                          3                           4
user2      0                          5                           5
--------------------------------------------------------------------

and my sql statemnt is:

SELECT Name, SUM(case when Action1 is null then Total Else 0 end) as [TotalAction1],
SUM(CASE WHEN Action2 = 'pending' THEN Total ELSE 0 END) AS [TotalAction2],
count(*) as Total FROM vTable GROUP BY Name, Seccode order by Name

the question is that i want to get the total number of each column like follow:

Name      TotalAction1        TotalAction2         Total
--------------------------------------------------------------------
user1      1                          3                           4
user2      0                          5                           5
Total        1                          8                           9
--------------------------------------------------------------------

I try to use the WITH CUBE but its not working and actually i dont know how to use it, please help me!!
LVL 1
AZZA-KHAMEESAsked:
Who is Participating?
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.

Mike EghtebasDatabase and Application DeveloperCommented:
SELECT Name, SUM(case when Action1 is null then Total Else 0 end) as [TotalAction1],
SUM(CASE WHEN Action2 = 'pending' THEN Total ELSE 0 END) AS [TotalAction2],
count(*) as Total FROM vTable Union Select "Total" As Name, Sum(Action1) as [TotalAction1], Sum(Action2) as [TotalAction2] From vTable GROUP BY Name, Seccode order by Name
0
Mike EghtebasDatabase and Application DeveloperCommented:
This is Hat you will get (if the query works):

Name      TotalAction1        TotalAction2         Total
--------------------------------------------------------------------
Total        1                          8                           9
user1      1                          3                           4
user2      0                          5                           5
--------------------------------------------------------------------

Unlees you use this:

SELECT " " & Name As TheNAme, SUM(case when Action1 is null then Total Else 0 end) as [TotalAction1],
SUM(CASE WHEN Action2 = 'pending' THEN Total ELSE 0 END) AS [TotalAction2],
count(*) as Total FROM vTable Union Select "Total" As TheNAme, Sum(Action1) as [TotalAction1], Sum(Action2) as [TotalAction2] From vTable GROUP BY Name, Seccode order by Name

to get:

TheName      TotalAction1        TotalAction2         Total
--------------------------------------------------------------------
 user1              1                          3                           4
 user2              0                          5                           5
Total                1                          8                           9
--------------------------------------------------------------------
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
try this (using ROLLUP), and remove the Seccode from the group by:

  SELECT COALESCE(Name, 'TOTAL') Name
    , SUM(case when Action1 is null then Total Else 0 end) as [TotalAction1]
    , SUM(CASE WHEN Action2 = 'pending' THEN Total ELSE 0 END) AS [TotalAction2]
    , count(*) as Total 
  FROM vTable 
  GROUP BY Name
  WITH ROLLUP  

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

AZZA-KHAMEESAuthor Commented:
i tryed the solution by eghtebas as i understand it by writting it like this:

SELECT EngName As TheNAme,
SUM(case when ActionType is null then Total Else 0 end) as [Assign],
SUM(CASE WHEN ActionType = 'pending' THEN Total ELSE 0 END) AS [Pending],
count(*) as Total FROM vTable
Union Select "Total" As TheNAme,
SUM(case when ActionType is null then Total Else 0 end) as [Assign],
SUM(CASE WHEN ActionType = 'pending' THEN Total ELSE 0 END) AS [Pending],
From vTable GROUP BY EngName, Seccode order by EngName

but i get an error massage in QueryAnalyser :
Incorrect syntax near the keyword 'From'.

and i tryied the solution by angelIII but i didnt got the correct result and my sql statement is:

SELECT COALESCE(EngName, 'TOTAL') Name,
SUM(case when ActionType is null then Total Else 0 end) as [Assign],
SUM(CASE WHEN ActionType = 'pending' THEN Total ELSE 0 END) AS [Pending],
count(*) as Total FROM vTable
GROUP BY EngName, Seccode
WITH ROLLUP  

and the Result was:

Name      TotalAction1        TotalAction2         Total
--------------------------------------------------------------------
user1      1                          1                           1
user1      0                          1                           1
user2      0                          0                           1
user2      1                          0                           1
Total        1                          1                           2
--------------------------------------------------------------------

which is wrong
please help me cuz i confused :(
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I repeat, change
GROUP BY EngName, Seccode
into
GROUP BY EngName

and the results will be as requested
0
AZZA-KHAMEESAuthor Commented:
Right, Done, Thank you.

last question
is there any way to change the color of the last row Total in sql statement insted of changing the color in code??
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sql returns data, and not a formatting.
now, what "code" are you speaking of?
if you have .net application, and load the data into a grid, you might want to generated that line not via sql, but using the .net facilities, and have the footer row colored differently indeed...
0
AZZA-KHAMEESAuthor Commented:
ok another question about the sql statement i want to order the record by EngName
but when i add the ORDER BY EngName the last row TOTAL become the first row and i want it to be the last row so how can i do this?


SELECT COALESCE(EngName, 'TOTAL') Name,
SUM(case when ActionType is null then Total Else 0 end) as [Assign], 
SUM(CASE WHEN ActionType = 'pending' THEN Total ELSE 0 END) AS [Pending],count(*) as Total 
FROM vTable 
GROUP BY EngName
WITH ROLLUP  
ORDER BY EngName

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
SELECT COALESCE(EngName, 'TOTAL') Name,
SUM(case when ActionType is null then Total Else 0 end) as [Assign], 
SUM(CASE WHEN ActionType = 'pending' THEN Total ELSE 0 END) AS [Pending],count(*) as Total 
FROM vTable 
GROUP BY EngName
WITH ROLLUP  
ORDER BY CASE WHEN EngName IS NULL THEN 1 ELSE 0 END, EngName

Open in new window

0

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
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 2005

From novice to tech pro — start learning today.