Oracle Group Function

Greeting,

I have the following sql in oracle but having error "group function is not allowed here".

Select ID,type,sum(amount)
from tbl
where type='M' and sum(amount) is not null
group by ID,type;

Any suggestions.

Thanks in advance.
mrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
Select ID,type,sum(amount)
from tbl
where type='M'
group by ID,type
having sum(amount) is not null;



you can't put the sum() in the where clause, that's what having is for
you could also do it with an inline view if you alias the result


select id, type,amount_sum from
(Select ID,type,sum(amount) amount_sum
from tbl
where type='M'
group by ID,type)
where amount_sum is not null
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
>>sum(amount) is not null

You cannot have a SUM in a where clause.

Try something like:

Select ID,type,mysum
(
Select ID,type,sum(amount) mysum
from tbl
where type='M'
group by ID,type
)
where mysum is not null;
0
sdstuberCommented:
note, the only way a SUM() could be null is if every row it was summing was null.  So you could also pre-filter by eliminating those


Select ID,type,sum(amount) amount_sum
from tbl
where type='M' and amount is not null
group by ID,type
0
Geert GOracle dbaCommented:
null is also possible with a partition by ...
0
sdstuberCommented:
sure, but that would be a totally different query structure because you'd be using the SUM analytic, not the SUM aggregate and hence no group by and different number of result rows unless you also applied distinct.

I guess I could have/should have been more specific


note, the only way a SUM() could be null

is more accurately written as

note, the only way a SUM() aggregate with that type of grouping could be null
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.