CODEWITHSUNDEEP
sqlsql-serverunion
Alec.
Alec.

Reputation: 5525

Create SQL summary using union

I currently have some SQL that is used to create an excel report in the following format:

COL1    COL2     COL3
  2       1        8
  3       7        9 
  1       2        4

Now what I am trying to do is sum up the total of these each value and insert it at the bottom using UNION ALL (unless of course there is a better way.)

Now the values for each column are generated already by sums. The concept I can't grasp is how to sum all the values for the final row, if this is even possible.

So the output should look like so:

COL1    COL2     COL3
  2       1        8
  3       7        9 
  1       2        4
  6       10       21

Thanks!

Upvotes: 0

Views: 518

Answers (3)

podiluska
podiluska

Reputation: 51494

It looks like you want to add

 WITH ROLLUP

to the end of your query

eg:

Select sum(a) as col1, sum(b) as col2
from yourtable
group by something
with rollup

Depending on the full nature of your query, you may prefer to use with cube, which is similar. See http://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx

Upvotes: 2

sion_corn
sion_corn

Reputation: 3141

select 
    col1
    ,col2
    ,col3
from tableA

union

select 
    sum(col1)
    ,sum(col2)
    ,sum(col3)
from tableA

order by col1,col2,col3

Upvotes: 1

g2server
g2server

Reputation: 5367

    SELECT COL1, COL2, COL3
    FROM SomeTable
    UNION ALL
    SELECT SUM(COL1), SUM(COL2), SUM(COL3)
    FROM SomeTable

note. there is also a ROLLUP clause but I think the above would be a simpler solution in this case http://technet.microsoft.com/en-us/library/ms189305%28v=sql.90%29.aspx

Upvotes: 0

Related Questions