Question 21396 in Mysql Question posted in
The official MySQL documentation can be found here.

Check for all null values in a GROUP BY – Mysql

I have the following structre

id val
1 ...
.
.
2 ...
.
.
3 null
3 null
3 null
4 ...
.
.

Basically each id has multiple no. of values. And an id has either all values as integers or all values as null

What I want is to perform an aggregate (like AVG) on val group by id. If that id has null values, I want to put 5 there.

#1

SELECT id, (CASE SUM(val) WHEN null THEN 5 ELSE AVG(val) END) AS ac FROM tt GROUP BY id
> executes ELSE even for id = 3

In CASE, there should be an aggregate function that when done on null values give null.
I checked SUM and MAX like

SELECT SUM(val) FROM tt WHERE id = 3
> null

and it gives null here but doesn’t work in main statement. I guess it is related to the type of equality and hence tried WHEN IS NULL but its a syntax error.

Also, is there some more standard way of indicating group of values as all null rather than using SUM or MAX.

Tags:

2

Answers


  1. 0

    The exact problem with your CASE expression is that to check for null in MySQL we have to use IS NULL rather than equality. So use this version:

    CASE WHEN SUM(val) IS NULL THEN 5 ELSE AVG(val) END

    But we might as well just use COALESCE() to assign an average of 5 for those id groups having all null values.

    SELECT id, COALESCE(AVG(val), 5) AS avg_val
FROM tt
GROUP BY id;

    Note that the AVG() function by default ignores nulls. Therefore, the expression AVG(val) would only be null if every record in an id group were having null for val.

    Login or Signup to reply.
  2. 0

    You can use if condition :

    select id, If(sum(val) is null, 5, AVG(val)) as average
FROM tt
group by id

    check here : https://dbfiddle.uk/Uso9nNTM

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search