Home » SQL & PL/SQL » SQL & PL/SQL » GROUP BY
GROUP BY [message #194234] Thu, 21 September 2006 04:40 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have a query which need to give group by on ITEM.
I have used the following query:
SELECT item, subord,TO_CHAR(supplydate,'MM/DD/YYYY'),
si_avg_fcst,oh,SUM(qty),SUM(qty1)
FROM item
GROUP BY item,subord,supplydate,si_avg_fcst,oh;
The output is as under :

ITEM	SUBORD	    SUPPLYDATE	   AVG_FCST	OH	24-Sep-06	1-Oct-06
001147	03G110DS	1/1/1970	7807	0	13515	1523
001147	03G110DS	1/1/2005	7807	0	6758	761
001201	001892	        1/1/1970	53	0	2295	0
001308	03G116DS	1/1/1970	784	0	1485	0
001448	13F104DS	1/1/1970	3347	0	0	755
001448	13F104DS	1/1/2006	3347	0	0	755
001457	13F111DS	1/1/1970	0	0	122998	0
001457	13F111DS	1/1/2005	0	0	61499	0


But the required output needs to show distinct item that is item should not be repeated.
The following output is required.
001147	03G110DS	1/1/1970	7807	0	20273	2284
001201	001892   	1/1/1970	53	0	2295	0
001308	03G116DS	1/1/1970	784	0	1485	0
001448	13F104DS	1/1/2006	3347	0	0	1510
001457	13F111DS	1/1/2005	0	0	184497	0



The dates ate getting the value as SUMMED under one item. Please advice what change i need to make to work this out.

Thanks,
Mona
Re: GROUP BY [message #194236 is a reply to message #194234] Thu, 21 September 2006 04:50 Go to previous messageGo to next message
kathyjomoore
Messages: 4
Registered: September 2006
Location: USA
Junior Member
Try

SELECT DISTINCT item, subord,TO_CHAR(supplydate,'MM/DD/YYYY'),
si_avg_fcst,oh,SUM(qty),SUM(qty1) 
FROM item 
GROUP BY item,subord,supplydate,si_avg_fcst,oh;
Re: GROUP BY [message #194237 is a reply to message #194234] Thu, 21 September 2006 04:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your posted results make it uncertain which SUPPLY_DATE you want to be displayed.
For Product 001147 you show the earliest date, and for 001448 and 001457 you show the latest.
By a majority of 2:1, I'll give you the latest:

(untested)
SELECT item
       ,subord
       ,TO_CHAR(min(supplydate),'MM/DD/YYYY') supplydate
       ,si_avg_fcst
       ,oh
       ,SUM(qty)
       ,SUM(qty1) 
FROM   item 
GROUP BY item,subord,si_avg_fcst,oh;
Re: GROUP BY [message #194238 is a reply to message #194234] Thu, 21 September 2006 04:54 Go to previous messageGo to next message
gojko
Messages: 18
Registered: September 2006
Location: London
Junior Member
It looks to me like you are trying to ignore different supply dates there

Since you are summarising these two rows

ITEM	SUBORD	    SUPPLYDATE	   AVG_FCST	OH	24-Sep-06	1-Oct-06
001147	03G110DS	1/1/1970	7807	0	13515	1523
001147	03G110DS	1/1/2005	7807	0	6758	761 


into:

001147	03G110DS	1/1/1970	7807	0	20273	2284


if that is the case, decide what you want to do which supply date you want to display. for example, choose to display the first supply date - then don't put supply date into group by, but wrap it into an aggregate function

SELECT item, subord, TO_CHAR(min(supplydate),'MM/DD/YYYY'),
si_avg_fcst,oh,SUM(qty),SUM(qty1) 
FROM item 
GROUP BY item,subord,si_avg_fcst,oh;


if differences in si_avg_fcst and oh should also be ignored, do the same for them.


Gojko Adzic
http://www.gojko.com
Re: GROUP BY [message #194245 is a reply to message #194238] Thu, 21 September 2006 05:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That query looks eerily familiar.....
Re: GROUP BY [message #194261 is a reply to message #194245] Thu, 21 September 2006 06:05 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks All,
You made my work easier. I can use the Min or Max function.

Regards,
Mona
Previous Topic: Matrix Type Results from Query (I could use some help)
Next Topic: DISPLAY VALUES IF NULL ALSO (important)
Goto Forum:
  


Current Time: Sat Dec 03 12:26:22 CST 2016

Total time taken to generate the page: 0.04743 seconds