Search code examples
mysqlgroup-by

Check for all null values in a GROUP BY

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.

Solution

  • 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