mysql IF in GROUP BY clause

group-by, if-statement, mysql

Solution

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

Problem

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 | ```

Original source