Query to total

From: The Magnet <art_at_unsu.com>
Date: Thu, 19 May 2011 12:55:54 -0700 (PDT)
Message-ID: <692216c9-ce79-42e1-9465-894113b80cc2_at_s9g2000yqm.googlegroups.com>

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) Received on Thu May 19 2011 - 14:55:54 CDT