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 Go to next message
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
Re: SUBTOTALS for the repeating row. [message #340624 is a reply to message #340620] Wed, 13 August 2008 08:10 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version with 4 decimals.


Regards
Michel

[Updated on: Wed, 13 August 2008 08:11]

Report message to a moderator

Previous Topic: Need a select query for the following scenario (merged)
Next Topic: DESC MATERIALIZED VIEW
Goto Forum:
  


Current Time: Fri Dec 02 12:34:31 CST 2016

Total time taken to generate the page: 0.09583 seconds