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

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 th开发者_运维问答anks

Dave

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

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

Try this:

SELECT ...
FROM your_table
GROUP BY
  CASE
    WHEN tableNum IS NOT NULL THEN tableNum
    ELSE deviceNum
  END

GROUP BY (IFNULL(child_id, parent_id))