OraFAQ Forum: Server Administration » aggregate functions and zero values

Home » RDBMS Server » Server Administration » aggregate functions and zero values

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

aggregate functions and zero values [message #371856]

Thu, 14 December 2000 10:20

Jon Oddvar Sellereite
Messages: 1
Registered: December 2000

Junior Member

How to use Avg, Min, Max and request this functions to neglect zero values. If I have the Sql statement: Select Sum(Field1), Avg(Field2) From
Where <...> Group by <....>, And I want the average to be calculated with only values greater than zero. "Avg(Field2>0)" ??

Report message to a moderator

Re: aggregate functions and zero values [message #371857 is a reply to message #371856]

Thu, 14 December 2000 11:51

Brent Stayer
Messages: 6
Registered: October 2000

Junior Member

Try the following:

Select avg(Field1), sum(Field1) from (select Field1 from
where Field1 > 0);

Hope this helps......
Brent

Report message to a moderator

Re: aggregate functions and zero values [message #371865 is a reply to message #371856]

Fri, 15 December 2000 03:36

John R
Messages: 156
Registered: March 2000

Senior Member

Or, removeing an entire level of subquery:

Select AVG(field_1),SUM(field_1)
from table_1
where NVL(field_1,0) !=0

No point getting over complex.

Report message to a moderator

Previous Topic:	Convert number to date
Next Topic:	What is the diff. between row and statement level trigger?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 03:13:09 CDT 2024