We help IT Professionals succeed at work.

Oracle Select Sum with Group By

JDCam
JDCam used Ask the Experts™
on
Experts,
See below select statement I am trying to write.

I need a total qty when grouped by lev1 and lev2
as-is I get the correct total by lev2

I CURRENTLY GET:
LEV1   LEV2   QTY(LEV2)  
ABC    123      50              
ABC    124      27              
DEF     321    100            
DEF     322      45            

I WANT:
LEV1   LEV2   QTY(LEV2)   QTY(LEV1)
ABC    123      50              77
ABC    124      27              77
DEF     321    100             145
DEF     322      45             145

I can I use more than 1 grouping in my totals?

SELECT
D5.ORD_NUM AS ORDNUM,
SUM(D5.ORD_SHIP_QTY) AS QTY,
SUM(D5.ORD_TOT_WGT) AS WGHT,
D5.ORD_LEV1 AS LEV1,
D5.ORD_LEV2 AS LEV2,
FROM E_ORD_D5 D5
LEFT JOIN M_ITEM_H M ON M.CUST_CODE = D5.CUST_CODE AND M.ITEM_CODE = D5.ORD_LEV1
WHERE D5.ORD_NUM = 604412 and D5.comp_code = 'W1'
GROUP BY D5.ORD_NUM, D5.ORD_LEV1, D5.ORD_LEV2
ORDER BY D5.ORD_LEV1, D5.ORD_LEV2
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
your group by and select include ord_num, but you aren't showing it in your sample data or expected results,  is that a mistake in your example or in your query?
Most Valuable Expert 2011
Top Expert 2012
Commented:
assuming the ordnum is supposed to be part of the results,  try this...


SELECT   ordnum,
         qty lev2qty,
         wght lev2wght,
         lev1,
         lev2,
         SUM(qty) OVER (PARTITION BY ordnum,lev1) lev1qty,
         SUM(wght) OVER (PARTITION BY ordnum,lev1) lev1wght
    FROM (SELECT   d5.ord_num AS ordnum,
                   SUM(d5.ord_ship_qty) AS qty,
                   SUM(d5.ord_tot_wgt) AS wght,
                   d5.ord_lev1 AS lev1,
                   d5.ord_lev2 AS lev2
              FROM     e_ord_d5 d5
                   LEFT JOIN
                       m_item_h m
                   ON m.cust_code = d5.cust_code AND m.item_code = d5.ord_lev1
             WHERE d5.ord_num = 604412 AND d5.comp_code = 'W1'
          GROUP BY d5.ord_num, d5.ord_lev1, d5.ord_lev2)
ORDER BY ordnum,lev1,lev2

Author

Commented:
yes sorry,
I deleted a dozen or more fields from the example to simplifiy
should look like this

I WANT:
ORDNum LEV1   LEV2   QTY(LEV2)   QTY(LEV1)
10001      ABC    123      50              77
10001      ABC    124      27              77
10001      DEF     321    100             145
10001      DEF     322      45             145
Most Valuable Expert 2011
Top Expert 2012

Commented:
try query above,  I made the assumption you also wanted to include the weights with the quantities and corresponding sums

Author

Commented:
sdstuber,
WOW! thats great. it will take me a while to digest this.
As-is it doesn't like the final ORDER BY line. If I remove it works, but may not be sorted corrected
Most Valuable Expert 2011
Top Expert 2012

Commented:
are the column names correct?


ordnum, lev1 and lev2  are all in the select list,  

if you real query uses something different that might be the problem.

Author

Commented:
Excellent !!