Re: Query to total

From: HeadLessBoot <headless_at_seattle.us.maybe>
Date: Fri, 20 May 2011 11:33:23 +0200
Message-ID: <4dd6356e$2_at_news.x-privat.org>

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

Received on Fri May 20 2011 - 04:33:23 CDT