mysql IF in GROUP BY clause

Question

Is there a way do the if in group by clause?

I have a query where I want to group the result based on the value of a column

if the column is Null, i want the result to remain as it is but if not, i want to group it by that value? how do you do that?

edit: sorry I think i should put a more specific example

the columns below contains the id of category, thread and reply

it's for a forum

the ones with null values means they don't have any reply in them

if the reply is null, i don't want to group it

the purpose is for counting the replies and the threads inside a category

i did not set the value for reply to be null, they are like that because of the result of a join

| category | thread | reply   |
-------------------------------
| 1        | 1      | 1       |
| 1        | 1      | 2       |
| 1        | 2      | 3       |
| 2        | 3      | 4       |
| 3        | 4      | 5       |
| 3        | 4      | 6       |
| 4        | 5      | null    |
| 5        | 6      | null    |

then the result would be

| category | thread | reply |
-----------------------------
| 1        | 3      | 3     |
| 2        | 1      | 1     |
| 3        | 2      | 2     |
| 4        | 1      | null  |
| 5        | 1      | null  |

Answer 1

As of mysql 5.6 you can use a IF() in the order by clause.

Like this:

ORDER BY IF(reply=NULL, 1, 0), category

Maybe not the right answer for your specific case, but handy for other people searching for something like this.

Answer 2

You can actually include a CASE statement in a GROUP BY or ORDER BY clause:

ORDER BY CASE
            WHEN reply IS NULL THEN 1  
            ELSE 0 
         END, category