Total Sum of all columns

Database

Total Sum of all columns

Hi All,

I run a query daily which groups some specific data and gives an integer value in several columns like

GROUP || NAME || Count(A) || Count (A+B) || Difference
========================================
GROUP A || GrA || 1234 || 1236 || 2
GROUP B || GrB || 1111 || 1116 || 5
GROUP C || GrC || 90 || 100 || 10

Is it possible to create an "extra row" in the result set that will contain a total sum of all those integer columns?
So that it will look something like

GROUP || NAME || Count(A) || Count (A+B) || Difference
========================================
GROUP A || GrA || 1234 || 1236 || 2
GROUP B || GrB || 1111 || 1116 || 5
GROUP C || GrC || 90 || 100 || 10
TOTAL || || 2435 || 2452 || 17

Thanks for any advice!
Tags (1)
4 REPLIES

Re: Total Sum of all columns

Take a look at WITH clause in the documentation.

HTH

Cheers.

Carlos.

Re: Total Sum of all columns

you can use a union stmt using sum stmt. But this will take two passes

N/A

Re: Total Sum of all columns

WITH is only supported in BTEQ, but extended grouping can be used instead.
Just modify your existing GROUP BY and ORDER BY:

GROUP BY GROUPING SETS((grp,name), ())
ORDER BY GROUPING(grp), grp

Dieter

Re: Total Sum of all columns

Thanks alot guys :)