Home » SQL & PL/SQL » SQL & PL/SQL » Statement with sum(Col1+Col2) in where clause (Oracle 9iR2)
Statement with sum(Col1+Col2) in where clause [message #315171] Sat, 19 April 2008 11:44 Go to next message
galaxy
Messages: 62
Registered: October 2005
Member
Hi,

I tried to write the following Query


select T1.autobrand,T1.model,max(T1.yearofconstruction)
from soldcars T1, Totalcars T2
where T1.autobrand=T2.autobrand
and T1.model=T2.model
and sum(T2.paid+T2.unpaid)<=0
group by T1.autobrand,T1.model;


but the part
"and sum(T2.paid+T2.unpaid)<=0"
does not work.

but I need to have this in my where clause because I just need to know the maximum year of construction date for cars where the sum for the 2 columns in T2 is equal or lowerer as zero.

Please take into account that the column names are fictitious because I am not allowed to use the correct names. But the issue is described above.

Thanks in advance for your help
Re: Statement with sum(Col1+Col2) in where clause [message #315173 is a reply to message #315171] Sat, 19 April 2008 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use HAVING clause.

Regards
Michel
Re: Statement with sum(Col1+Col2) in where clause [message #315274 is a reply to message #315171] Sun, 20 April 2008 13:45 Go to previous messageGo to next message
galaxy
Messages: 62
Registered: October 2005
Member
I do not understand this. Where should I use the having clause.
As far as I know having can only be used with count.

But how should I use having clause to say:
"and sum(T2.paid+T2.unpaid)<=0"

Thanks for your help
Re: Statement with sum(Col1+Col2) in where clause [message #315277 is a reply to message #315274] Sun, 20 April 2008 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As far as I know having can only be used with count.

This is wrong.

Quote:
But how should I use having clause to say:
"and sum(T2.paid+T2.unpaid)<=0"

As you wrote it.

Regards
Michel

Re: Statement with sum(Col1+Col2) in where clause [message #315296 is a reply to message #315171] Mon, 21 April 2008 00:18 Go to previous message
kecd_deepak
Messages: 52
Registered: December 2007
Member
When we use aggregate Functions , we use HAVING clause to restrict data groups.

The hierarchy of execution of an sql statement is:

FROM
WHERE
Group By
HAVING

You use SUM Function in Where clause, Which is unknown for Where clause, Since WHERE clause restrict data before creation of Groups.
Previous Topic: Need sql to create test data , already searched the old posts
Next Topic: how to find missing numbers
Goto Forum:
  


Current Time: Sun Dec 04 10:59:13 CST 2016

Total time taken to generate the page: 0.11376 seconds