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.

I have been playing with CUBE or GROUPING SETS with no luck. Does
anyone here happen to have a good idea?

SELECT group_name, sales, cancelled, sales - cancelled net_sales,
provider_fee, sales - cancelled - provider_fee gross_profit,
authorize_fee, authorize_fee_canc, credit_card_fee,
partner_fee
FROM (SELECT group_id, group_name, NVL(sales,0) sales, NVL(cancelled,
0) cancelled, NVL(provider_fee,0) provider_fee,
NVL(authorize_fee,0) authorize_fee, NVL(authorize_fee_canc,
0) authorize_fee_canc, 0 credit_card_fee,
NVL(partner_fee,0) partner_fee
FROM (SELECT group_id, group_name, ROW_NUMBER() OVER (PARTITION BY
group_name ORDER BY group_name) rnum,
(SELECT SUM(order_quantity * price)
FROM reports.newsletter_report nr
WHERE pg.newsletter_id = nr.newsletter_id
AND date_entered >= SYSDATE - 2
AND date_entered < SYSDATE
AND transaction_id <> -1
AND transaction_id IS NOT NULL
GROUP BY group_name) sales,
(SELECT SUM(c.amount)
FROM reports.newsletter_report nr, reports.cancellation c
WHERE pg.newsletter_id = nr.newsletter_id
AND c.order_item_id = nr.order_item_id
AND c.cancel_date >= SYSDATE - 2
AND c.cancel_date < SYSDATE
AND c.transaction_id <> -1
AND c.transaction_id IS NOT NULL
GROUP BY group_name) cancelled,
(SELECT SUM(royalty * price / 100)
FROM reports.newsletter_report nr
WHERE pg.newsletter_id = nr.newsletter_id
AND date_entered >= SYSDATE - 2
AND date_entered < SYSDATE
AND transaction_id <> -1
AND transaction_id IS NOT NULL
GROUP BY group_name) provider_fee,
(SELECT SUM(0.1)
FROM reports.newsletter_report nr
WHERE pg.newsletter_id = nr.newsletter_id
AND date_entered >= SYSDATE - 2
AND date_entered < SYSDATE
AND transaction_id <> -1
AND transaction_id IS NOT NULL
GROUP BY group_name) authorize_fee,
(SELECT SUM(0.1)
FROM reports.newsletter_report nr, reports.cancellation c
WHERE pg.newsletter_id = nr.newsletter_id
AND c.cancel_date >= SYSDATE - 2
AND c.cancel_date < SYSDATE
AND c.order_item_id = nr.order_item_id
AND c.transaction_id <> -1
AND c.transaction_id IS NOT NULL
AND (nr.customer_id, nr.order_item_id) IN (SELECT
DISTINCT customer_id, order_item_id

FROM reports.newsletter_report

WHERE date_entered >= SYSDATE - 2

AND date_entered < SYSDATE)) authorize_fee_canc,
(SELECT SUM(rate * price / 100)
FROM reports.newsletter_report nr
WHERE pg.newsletter_id = nr.newsletter_id
AND date_entered >= SYSDATE - 2
AND date_entered < SYSDATE
AND transaction_id <> -1
AND transaction_id IS NOT NULL
GROUP BY group_name) partner_fee
FROM reports.product_groups pg)
WHERE rnum = 1
ORDER BY group_id)

The Magnet, on 05/19/2011 09:55 PM, wrote:
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

HeadLessBoot, on 05/20/2011 11:33 AM, wrote:

<CUT>

Sorry, I obviously forgot to end it all with
FROM DUAL

On May 20, 4:36*am, HeadLessBoot <headl... (AT) seattle (DOT) us.maybe> wrote:

Well, this is working. But it does not have what I need. You see by
my original query that certain columns have a different set of
criteria, which I am not sure how to integrate into this query:


SELECT DECODE(group_name,NULL,'TOTAL',group_name) group_name, sales,
provider_fee, authorize_fee, partner_fee, 0 credit_card_fee
FROM (SELECT group_name, SUM(order_quantity * price) sales,
SUM(royalty * price / 100) provider_fee, SUM(0.1) authorize_fee,
SUM(rate * price / 100) partner_fee
FROM reports.newsletter_report nr, reports.product_groups pg
WHERE pg.newsletter_id = nr.newsletter_id
AND nr.date_entered >= SYSDATE - 2
AND nr.date_entered < SYSDATE
AND nr.transaction_id <> -1
AND nr.transaction_id IS NOT NULL
GROUP BY CUBE (pg.group_name));

On 20.05.2011 11:33, HeadLessBoot wrote:
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/E...2.htm#i2161315


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