Home » SQL & PL/SQL » SQL & PL/SQL » SUBTOTALS for the repeating row. (Oracle 8i, windows NT.)
SUBTOTALS for the repeating row. [message #340620] |
Wed, 13 August 2008 07:57  |
saudalnasr
Messages: 7 Registered: August 2008
|
Junior Member |
|
|
hello friends,
my question is i want to get subtotals for "TOT_VAL" column, only if the itemcode repeats.
i have tried using rollup and cube function, however unable to get the desired result.
any help will be highly appreciateable
below mentioned is my query
SELECT Item_Code,
Item_desc,
Item_grp_Code,
Item_Subgrp_Code,
Location,
Locn_Name,
txn_Code,
MAX(Doc_No) Last_Doc,
MAX(Document_dt) lst_Doc_dt,
SUM(qty) Tot_qty,
SUM(val) Tot_val,
Floor(SYSDATE - MAX(Document_dt)) AgeIng
FROM (SELECT sl_comp_Code Company,
Item_Anal_Code_1 Prod_Class_Code,
Anal_Name Prod_Class_Name,
sl_Locn_Code Location,
Locn_Name,
Item_ig_Code Item_grp_Cope,
Item_Isg_Code Item_Subgrp_Code,
sl_Item_Code Item_Code,
Item_Name Item_desc,
sl_txn_Code txn_Code,
sl_No Doc_No,
sl_dt Document_dt,
sl_qty qty,
sl_wac Rate,
sl_val val,
Round(SYSDATE - sl_dt,0) AgeIng
FROM os_stk_Ledger os,
om_Item om,
om_Analysis oa,
om_Location ol
WHERE om.Item_Code = os.sl_Item_Code
AND om.Item_Anal_Code_1 = oa.Anal_Code
AND sl_txn_Code IN ('GRN',
'SEH',
'GRC',
'GRA',
'SCR',
'GRS')
AND oa.Anal_No = 1
AND os.sl_Locn_Code = ol.Locn_Code
AND sl_dt BETWEEN '10-AUG-08'
AND '13-Aug-08'
ORDER BY Item_Code,
Location)
GROUP BY Item_Code,
Item_desc,
Item_grp_Cope,
Item_Subgrp_Code,
Location,
Locn_Name,
txn_Code
in order to fit the column in the given space and describe my point clearly i have left out some of the column from my query.however in the actual resultset i need all the column mentioned in my query
Item_Code Item_desc Item_grp_Code Location Tot_qty Tot_val
12 glass 03 186 12 120
12 glass 01 192 15 150
TOTAL 270
98 mirror 02 192 10 190
32 alm.gl 01 98M 50 25
457 pargls 06 72R 46 350
457 pargls 06 36M 23 175
TOTAL 525
|
|
|
|
Goto Forum:
Current Time: Fri Feb 14 14:24:59 CST 2025
|