Home » SQL & PL/SQL » SQL & PL/SQL » Sql query using Sum function and Having clause (Oracle 10g)
Sql query using Sum function and Having clause [message #299063] Fri, 08 February 2008 14:52 Go to next message
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 #299065 is a reply to message #299063] Fri, 08 February 2008 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must not group by t2.QTY_1, t2.QTY_2, t2.QTY_3, just by product.

Regards
Michel
Re: Sql query using Sum function and Having clause [message #299066 is a reply to message #299065] Fri, 08 February 2008 15:16 Go to previous messageGo to next message
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 #299067 is a reply to message #299066] Fri, 08 February 2008 15:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Michel
Re: Sql query using Sum function and Having clause [message #299069 is a reply to message #299067] Fri, 08 February 2008 16:28 Go to previous messageGo to next message
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 Go to previous message
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,

Previous Topic: data access path in oracle
Next Topic: Rank function
Goto Forum:
  


Current Time: Sat Dec 10 01:26:33 CST 2016

Total time taken to generate the page: 0.11356 seconds