JDCam
asked on
Oracle Select Sum with Group By
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
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
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
try query above, I made the assumption you also wanted to include the weights with the quantities and corresponding sums
ASKER
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
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
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.
ordnum, lev1 and lev2 are all in the select list,
if you real query uses something different that might be the problem.
ASKER
Excellent !!