Query SUM then Total within the same query

Database
Enthusiast

Query SUM then Total within the same query

I have a table of data that I am query against with the following columns: BILLING_NO BILL_MONTH_DT  BILL_CYCLE_DT  CHRGE_AMT ACCOUNT_NO.

The BILLING_NO is the same for all records (same customer). The ACCOUNT_NO is unique for each row.

So a typical ACCOUNT_NO could have 4 rows (+/-) with a CHRGE_AMT listed, and there are hundreds of rows.

Example:

BILLING_NO BILL_MONTH_DT BILL_CYCLE_DT CHRGE_AMT ACCOUNT_NO

123456789 4/1/2012  4/10/2012  108.21  00.ABCD.412349.   .DCBA.

123456789 4/1/2012  4/10/2012  114.46  00.ABCD.412349.   .DCBA.

123456789 4/1/2012  4/10/2012  43.00  00.ABCD.412349.   .DCBA.

123456789 4/1/2012  4/10/2012  24.00  00.ABCD.412349.   .DCBA.

I have built the query to SUM up the individual CHRGE_AMT as Sum(CHRGE_AMT).

Result: ( This results in over 400+ rows of data again with the ACCOUNT_NO

 being different for each row)

BILLING_NO  BILL_MONTH_DT  BILL_CYCLE_DT   Sum(CHRGE_AMT)   ACCOUNT_NO

123456789   4/1/2012             4/10/2012            289.67                    00.ABCD.412349.   .DCBA.

What I would like to be able to do is now Total all of the Sum(CHRGE_AMT) into 1 row as a Total_Amount.

Can this be done? I have learned that within the Teradata db I am working there are no UDF installed and I do not have access to install them, so I would need a SQL approach - I am guessing.

Any help is appreciated.

2 REPLIES
Highlighted
Junior Contributor

Re: Query SUM then Total within the same query

You need a kind of "Total Sum"?

As aa additional row it's changing the existing Group By to

GROUP BY GROUPING SETS((previous group by),())

For sorting the result set you need to do:

ORDER BY GROUPING(sortcol),sortcol

If you need it as a new column it's an OLAP function:

SUM(Sum(CHRGE_AMT)) OVER ()

Dieter

Enthusiast

Re: Query SUM then Total within the same query

Dieter,

This was exactly what I was in need of:

If you need it as a new column it's an OLAP function:

SUM(Sum(CHRGE_AMT)) OVER ()

by limiting my GROUP BY TO BILLING_NO, BILL_MONTH_DT, BILL_CYCLE_DT the query performs the aggregate function but only displays a single line of data as I needed.

Thanks for you assistence!

Regards,

GMoney