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!!
AZZA-KHAMEESAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
All Courses

From novice to tech pro — start learning today.