SQL aggregate functions using MIN and AVG...

Question

How can I omit Zeros in my data?

For example using the MIN function? I would like the Minimum value except a 0...

How can I get the next largest?

MIN(availables.price)

Also is there a generally way to skip over 0s in the same way if I'm using the AVG function? The problem is the table I've inherited doesn't use NULL values but has 0.00 for all financial data.

Thanks,

Answer 1

SELECT  MIN(CASE price WHEN 0 THEN NULL ELSE price END)
FROM    availables

Answer 2

try:

SELECT
    MIN(x)
    FROM ....
    where x<>0

works with AVG too:

SELECT
    avg(x)
    FROM ....
    where x<>0

Answer 3

Just leave the zeros out of the query with a where clause.

select min(price)
from price_table
where price > 0;

select avg(price)
from price_table
where price > 0;

Answer 4

SELECT MIN(availables.price) FROM availables WHERE availables.price <> 0.0

You can also use > if you want to exclude anything less than 0. Be careful about floating point rounding issues if you are using float or smallfloat data types.

If there are NULLs, you can also wrap the where clause column in a ISNULL(availables.price, 0.0) to exclude those as well.

Answer 5

SELECT  MIN(price)
FROM    availables
WHERE price <> 0

Answer 6

Hi,

Maybe it could do the work

SELECT MIN(availables.price)
FROM   availables
WHERE  0 < availables.price

Answer 7

are you using ruby on rails? i supposed you are since this is tagged as ruby-on-rails, in that case, you can simply do

Available.minimum(:price, :conditions => "price > 0")

or

Available.average(:price, :conditions => "price > 0")

hope that helps =)