Home » SQL & PL/SQL » SQL & PL/SQL » Help on Group subquery (Oracle 10g, Win XP)
Help on Group subquery [message #292195] Tue, 08 January 2008 03:32 Go to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi all.....

I am trying to run following SQL

select n1,sum(n2)
from 
(select 'R10' N1, 222 N2
from dual
union all
select ' ' N1, 10 N2
from dual
union all
select  'R12' N1, 400 N2
from dual)a
group by n1 
having  n1 is not null


and my desired result is
N1    Sum(N2)
------------
R12   400
R10   222


but I am getting an extra row for blank value....
Please sugest how to take it out...


from
freakabhi

[Updated on: Tue, 08 January 2008 03:44] by Moderator

Report message to a moderator

Re: Help on Group subquery [message #292199 is a reply to message #292195] Tue, 08 January 2008 03:41 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

why don't you use a WHERE clause.


regards,
Re: Help on Group subquery [message #292201 is a reply to message #292195] Tue, 08 January 2008 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Then, you have a line with n1 is one space and is not null, so why shouldn't be appeared?

In addition, use WHERE and not HAVING when condition is not on aggregate value.

Regards
Michel
Re: Help on Group subquery [message #292214 is a reply to message #292201] Tue, 08 January 2008 03:56 Go to previous messageGo to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi.......

I already tried using Where but results were same as now,,
Re: Help on Group subquery [message #292218 is a reply to message #292214] Tue, 08 January 2008 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Then, you have a line with n1 is one space and is not null, so why shouldn't be appeared?

Regards
Michel
Re: Help on Group subquery [message #292222 is a reply to message #292218] Tue, 08 January 2008 04:07 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

Quote:

I already tried using Where but results were same as now

can you post that.and don't forget to format your code.


regards,
Re: Help on Group subquery [message #292223 is a reply to message #292214] Tue, 08 January 2008 04:08 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Did you intend that you ' ' value was actually a '' value i.e. do you want there to be a null in that result set (and you simply mis-typed), or do you think that '[space]' represents a null. If it is the latter, then your assumption is incorrect.
Re: Help on Group subquery [message #292226 is a reply to message #292222] Tue, 08 January 2008 04:16 Go to previous message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
thanks got it.....

I was typing space in der and not null.....
thanks a lot.
Previous Topic: PL-Sql function
Next Topic: Simultaneous UPDATE/SELECT, is this possible
Goto Forum:
  


Current Time: Tue Dec 06 15:59:48 CST 2016

Total time taken to generate the page: 0.06069 seconds