CODEWITHSUNDEEP
mysqlaverage
Javier Enríquez
Javier Enríquez

Reputation: 630

Mysql make AVG return null if any value is null

I'm trying to take an AVG from one of my columns in MySQL. However, the calculation must be done in another way if some of the values are null. So, I'm trying to make this:

I want SELECT AVG(amount) from sales to return NULL if any row in the sales table has amount NULL. Can this be done?

Upvotes: 1

Views: 2561

Answers (3)

xQbert
xQbert

Reputation: 35323

I'm sure there's several ways to do this... This is one way.

I use a cross join to return the same value for all records if that value is > than 1 then a null record exists and I used a case statement to negate the avg and instead return null. This could just as easily be done in a function call..

SELECT case when B.c>1 then null else avg(amount) end as myAvg 
FROM Sales 
CROSS JOIN (
  SELECT count(*) c FROM Sales 
  WHERE amount is null) B
GROUP BY  B.C

Upvotes: 1

Michał Szkudlarek
Michał Szkudlarek

Reputation: 1502

I would try with (it counts nulls as zeros):

SELECT AVG(IFNULL(amount,0)) from sales

Edited (misinterpreted the question before):

SELECT IF(sum(amount is NULL), NULL, AVG(amount)) from sales

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use the following query:

SELECT CASE WHEN (SELECT COUNT(*)
                  FROM Sales
                  WHERE amount = 0 OR amount IS NULL) <> 0 
                  THEN NULL 
            ELSE AVG(amount)  END
FROM Sales

The above uses a sub-query in a CASE expression to check for 0 or NULL values. If any one value of amount field is 0 or NULL then the whole average is returned as NULL, otherwise the average value of amount is returned.

Demo here

Upvotes: 1

Related Questions