Re: Query to total

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sat, 21 May 2011 16:44:14 +0200
Message-ID: <93q1e5Faa9U1_at_mid.individual.net>

On 20.05.2011 11:33, HeadLessBoot wrote:
> The Magnet, on 05/19/2011 09:55 PM, wrote:
>> Hi,
>>
>> I have a real messed up query. It generates a number of numeric
>> columns. I need to generate a grand total row at the bottom.
>
> One quick and dirty trick is to make first a view of your query,
> just for the sake of simplicity in writing this:
>
> SELECT *
> FROM (
> THE_VIEW
> )
> UNION
> SELECT NULL
> , NULL -- as many NULLs
> , NULL -- as the fields
> , NULL -- where you don't need totals
> , NULL
> , NULL
> , NULL
> , NULL
> -- as many of these as your fields
> -- needing a total:
> , (
> SELECT SUM(NUMERICFIELD1) FROM THE_VIEW
> ) AS TOTAL1
> , (
> SELECT SUM(NUMERICFIELD2) FROM THE_VIEW
> ) AS TOTAL2
> , (
> SELECT SUM(NUMERICFIELD3) FROM THE_VIEW
> ) AS TOTAL3
> , (
> SELECT SUM(NUMERICFIELD4) FROM THE_VIEW
> ) AS TOTAL4
With that approach all the individual SELECTS are superfluous - if not harmful. Also, you want UNION ALL in order to ensure that the totals line does not disappear and probably also for better performance. Better:

SELECT *

FROM      (
           THE_VIEW
           )

UNION ALL
SELECT NULL

         , NULL  -- as many NULLs
         , NULL  -- as the fields
         , NULL  -- where you don't need totals
         , NULL
         , NULL
         , NULL
         , NULL
         -- as many of these as your fields
         -- needing a total:
	, SUM(NUMERICFIELD1) AS TOTAL1
	, SUM(NUMERICFIELD2) AS TOTAL2
	, SUM(NUMERICFIELD3) AS TOTAL3
	, SUM(NUMERICFIELD4) AS TOTAL4

You can even do that in a single query:

WITH THE_VIEW AS (SELECT * original query), SELECT ....
FROM THE_VIEW
UNION ALL
SELECT null, null, ..., SUM(c1), SUM(c2) FROM THE_VIEW Kind regards

        robert

PS: OP, I agree, it's a messed up query. Btw, I believe you can get rid of "c.transaction_id IS NOT NULL" when doing "c.transaction_id <> -1" because the latter is never true on a NULL column.

Also, you can reduce redundancy a lot by placing your repetitive select from cancellation and probably more in subquery factoring clause(s).

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2161315

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Received on Sat May 21 2011 - 09:44:14 CDT