Home » SQL & PL/SQL » SQL & PL/SQL » GROUP BY clause
GROUP BY clause [message #192148] Mon, 11 September 2006 04:36 Go to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Hi,
I have a query where I have to display 4 columns.
All the four have to be shown and there is a group by function.

Select a,b,c,d from <table1>
group by a,b,c,d.
Select a,b,SUM(SELECT SUM(c) FROM<table2> where condition),d
group by a,b,d.
result of c comes from a subquery and I have to use a SUM for c in its subquery.
Now when I have to show a,b,c,d together, I again have to do a,b,SUM(c),d group by a,b,d
This way c is SUMMED twice.
Please give me a suggestion as how can this be avoided.

Regards,
Sonali

[Updated on: Mon, 11 September 2006 04:45]

Report message to a moderator

Re: GROUP BY clause [message #192155 is a reply to message #192148] Mon, 11 September 2006 05:11 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Hi Sonali,

This is looking like the same solution given by me at the end of the below link, plz find it and try yourslef and let me know if you have any furthur issues...

http://www.orafaq.com/forum/t/69124/0/


Thks & Rgds
Venkat
Re: GROUP BY clause [message #192162 is a reply to message #192155] Mon, 11 September 2006 05:40 Go to previous messageGo to next message
sjaiswal
Messages: 31
Registered: September 2006
Location: Mumbai
Member
Thanks Venkat,
Actually my query is little complex and I doubt if the subqueries can help.
Please have a look at my select statement.

SELECT e.item,e.ordersum(i.avg_si),sum(i.qty) 
from item i, etg e
where i.qty in (SELECT sum(i.qty) from item i 
           WHERE i.date >= (TO_DATE(sysdate,'dd-mon-yy') +14)  
          AND po.needdate < (TO_DATE(sysdate,'dd-mon-yy') + 20 )
               )
group by e.item,e.order


In the above code you can see that the i.qty is getting Summed twice.

Regards,
Sonali
Re: GROUP BY clause [message #192166 is a reply to message #192148] Mon, 11 September 2006 05:53 Go to previous message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Hi Sonali,

First of all, whats the purpose of this query??

In the where clause you are trying to validate the "i.qty" with the sum of the quantity for a particular period, Is it correct??

plz give the table structures and Insert statement and the desired output here.

With Regards
Venkat.
Previous Topic: SubQuery not running
Next Topic: using a sequence in a view
Goto Forum:
  


Current Time: Fri Dec 02 13:49:02 CST 2016

Total time taken to generate the page: 0.05058 seconds