Sql query using Sum function and Having clause [message #299063] |
Fri, 08 February 2008 14:52  |
msharma
Messages: 6 Registered: January 2008
|
Junior Member |
|
|
HI,
I am trying to write an sql query which uses the Sum function along with Having clause.
I have 2 tables with 4 columns of interest – these are Product, Qty1, Qty2 and Qty3 present in both these tables. I need to sum these columns for table1 (there are several rows in table1 that need to be summed up but only 1 row per Product in table2 ) and if the values of Sum(Qty1), Sum(Qty2) and Sum(Qty3) of table1 are equal to those of table2, I need to select the Product.
My sql for this is as follows:
select PRODUCT
from t1, t2, t3, t4, t5
where cond1
and cond2
and cond3
and cond4
and cond5
and cond6
group by PRODUCT, t2.QTY_1, t2.QTY_2, t2.QTY_3
having SUM(t1.QTY_1) = t2.QTY_1
and SUM(t1.QTY_2) = t2.QTY_2
and SUM(t1.QTY_3) = t2.QTY_3
The above works just fine and produces expected results. My problem is that now the requirement has changed and there can be several rows in table2 also which need to be summed up and compared to the Sum(Qty1), Sum(Qty2) and Sum(Qty3) in table1.
So I changed my sql to below:
select PRODUCT
from t1, t2, t3, t4, t5
where cond1
and cond2
and cond3
and cond4
and cond5
and cond6
group by PRODUCT, t2.QTY_1, t2.QTY_2, t2.QTY_3
having SUM(t1.QTY_1) = SUM(t2.QTY_1)
and SUM(t1.QTY_2) = SUM(t2.QTY_2)
and SUM(t1.QTY_3) = SUM(t2.QTY_3)
and tried changing the group by clause to also include all columns from t1. I have tried various different sql statements, but nothing works! It was fine so long as table2 did not have several columns that need to be summed, but now this does not work at all.
Any help will be much appreciated!
Many thanks in advance.
|
|
|
|
Re: Sql query using Sum function and Having clause [message #299066 is a reply to message #299065] |
Fri, 08 February 2008 15:16   |
msharma
Messages: 6 Registered: January 2008
|
Junior Member |
|
|
Hi Michel,
I have tried this and it does not give the results expected.
The problem is that when I group just by PRODUCT, and I have 2 rows in table1 giving SUM(QTY1) = 3 for productA and
1 row in table2 for productA - also giving SUM(QTY1) = 3 the 1 row gets counted twice and the comparison becomes 3=6,
therefore I get no results.
I appreciate your response, however this is not the solution, I have tried this and it does not work.
Thanks anyways.
|
|
|
|
Re: Sql query using Sum function and Having clause [message #299069 is a reply to message #299067] |
Fri, 08 February 2008 16:28   |
msharma
Messages: 6 Registered: January 2008
|
Junior Member |
|
|
I will try to get together some table create and data insert scripts which I can post. However, there are no errors in the join conditions as my first sql query works fine - as soon as the having clause changes to
having SUM(t1.QTY_1) = SUM(t2.QTY_1)
instead of
having SUM(t1.QTY_1) = t2.QTY_1
the sql stops working.
The first sql query is identical to the second except the HAVING clause - I suspect I need to use UNION or some other approach to this completely.
|
|
|
Re: Sql query using Sum function and Having clause [message #299079 is a reply to message #299063] |
Fri, 08 February 2008 22:41  |
mshrkshl
Messages: 247 Registered: September 2006 Location: New Delhi
|
Senior Member |
|
|
SQL> select inv_id,sum(inv_amt),sum(amt_paid)
2 from inv
3 group by inv_id;
INV_ID SUM(INV_AMT) SUM(AMT_PAID)
---------- ------------ -------------
1 1600 350
2 2000 450
SQL> ed
Wrote file afiedt.buf
1 select inv_id,sum(inv_amt),sum(amt_paid)
2 from inv
3 group by inv_id
4* having sum(inv_amt)=sum(amt_paid)
SQL> /
no rows selected
SQL> ed
Wrote file afiedt.buf
1 select inv_id,sum(inv_amt),sum(amt_paid)
2 from inv
3 group by inv_id
4* having sum(inv_amt)=amt_paid
SQL> /
having sum(inv_amt)=amt_paid
*
ERROR at line 4:
ORA-00979: not a GROUP BY expression
for further solution
Quote: | Post a simple but complete test case.
We don't have your data, we don't have your tables, we don't know what the joining clauses; we don't know what you do, we can't reply accurately but most likely you make an error in the joining clause or your specifications are not clear or correct.
|
regards,
|
|
|