group by a field, but if empty group by another field

Question

I have a table that has a column called tableNum and another column called deviceNum

I would like to group by tableNum, but if tableNum is empty then I would need to group by deviceNum.

I have currently done this with a union statement so that I could do two different statements, but wondered if there were a better way.

Many thanks

Dave

Answer 1

You can group by coalesce/ifnull (they're the same in this example):

GROUP BY COALESCE (tableNum, deviceNum)
GROUP BY IFNULL (tableNum, deviceNum)

See the documentation:

http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#function_coalesce

You might have some meaningless grouping collisions between tableNum and deviceNum, should the two columns hold identical values. Depending on what type of result you want to get, you could then write something like this, to avoid collisions:

GROUP BY CASE WHEN tableNum IS NOT NULL 
              THEN CONCAT('tableNum : ', tableNum)
              ELSE CONCAT('deviceNum : ', deviceNum)
              END

This might be quite slow, though, as with CONCAT there is no chance of using indexes... Just to give you an idea. Note: as ypercube stated, UNION's thend to be faster because MySQL can parallelise subqueries

Answer 2

Keep the UNION and make it work, as your requirements, with UNION ALL.

It will probably be (or can be optimized to run) much faster than other solutions.

I wouldn't like to translate this or have to optimize it without UNION:

    SELECT tableNum
         , NULL AS deviceNum
         , COUNT(DISTINCT deviceNum) AS cnt
    FROM TableX
    WHERE tableNum IS NOT NULL
    GROUP BY tableNum 
UNION ALL
    SELECT NULL
         , deviceNum
         , COUNT(*)
    FROM TableX
    WHERE tableNum IS NULL
    GROUP BY deviceNum