Home » SQL & PL/SQL » SQL & PL/SQL » Help (oracle 10g)
Help [message #430038] Sat, 07 November 2009 04:18 Go to next message
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Test Case

outlet desciption sum(2008) sum(2009)
1 shoe 11 22
1 shirt 33 44
1 sandal 55 66
2 shoe 77 77
2 shirt 88 89
2 sandal 99 96

below code

SELECT sls.outlet  ,prefc.description,
   SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY') AND TO_DATE('31-OCT-2008','DD-MON-YYYY')
          then slsd.amt1
          else 0 end) "2008_data",
 SUM(case when sls.docdt BETWEEN TO_DATE('01-JAN-2009','DD-MON-YYYY')
               AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
          then slsd.amt1
          else 0 end) "2009_data"
  FROM sls_head sls          ,
 sls_detail slsd             ,
 prodfle pf                  ,
 prodref pref                ,
 prodref_classification prefc,
 customer c                  ,
 ctypes ct
 WHERE sls.docno=slsd.docno
AND sls.outlet    =slsd.outlet
AND sls.docdt    =slsd.docdt
AND sls.tc       =slsd.tc
AND sls.docdt BETWEEN TO_DATE('01-JAN-2008','DD-MON-YYYY') AND TO_DATE('31-OCT-2009','DD-MON-YYYY')
AND slsd.prodcd            =pf.prodcd
AND pf.refcode             =pref.refcode
AND pf.class_code          =prefc.class_code
AND c.outlet                =sls.outlet
AND c.cusno                =sls.cusno
AND c.custype              =ct.code
--AND prefc.refcode         IN ('A')
and prefc.class_code in ('AA','AB','AC')
GROUP BY sls.outlet,

there s other descriptions called

AD trousers
AE Tie

and all these(AA AB AC AD AE) descriptions comes under Refcode 'A'.

And i wants to find the sum of all ie., total

I wanna show the data like following..
Test case for that

outlet 'total_prod' sum_08(AA+AB+AC+AD+AE) sum_09(AA+AB+AC+AD+AE)

1 total_prod sum_08(AA+AB+AC+AD+AE) sum_09(AA+AB+AC+AD+AE)
2 total_prod sum_08(shoe+shirt+sandal) sum_09(shoe+shirt+sandal)
3 total_prod sum_08(shoe+shirt+sandal) sum_09(shoe+shirt+sandal)

Please help me to write code since am facing problems..

And using the same tables which i used in the above code..

Thanks Seyed
Re: Help [message #430047 is a reply to message #430038] Sat, 07 November 2009 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65138
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A test case include CREATE TABLE and INSERT statements.
A specification include a description of what should be the result with words.

So both of them and the result you want with the data you provide.

Re: Help [message #430156 is a reply to message #430038] Mon, 09 November 2009 04:59 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd probably do that by using your current query as an inline view, and doing something like
SELECT outlet
      ,sum(case when description in ('AA','AB','AC','AD','AE') then 2008_DATA else 0 end)
      ,sum(case when description in ('shoe','shirt','sandal' then 2008_data else 0 end)
FROM (...) 

Also never use lower case double quoted column names (like "2008_data") - it's a huge amount of hassle in the long run. Just call the column DATA_2008.
Previous Topic: stored procedure cursor and loop
Next Topic: solution to mutating trigger
Goto Forum:

Current Time: Fri Aug 18 00:03:58 CDT 2017

Total time taken to generate the page: 0.05012 seconds