SQL IF condition if GROUP BY statement

Question

I have some question about IF condition in SQL Is it possible to use the next syntax in SQL query? I`m interesting about if condition in group by statement

"SELECT * FROM TABLE WHERE... IF(order_id !=0, GROUP BY order_id, GROUP BY other_field)"

Answer 1

SELECT * 
FROM TABLE 
GROUP BY case when order_id <> 0 
              then order_id
              else other_field
         end

Answer 2

First, you shouldn't be doing select * with group by. The query would (normally) be rejected with a syntax error in most databases.

Second, the SQL standard is case.

Perhaps you want something like this:

select (case when order_id != 0 then order_id end) as order_id,
       (case when order_id = 0 then other_field end) as other_field,
       count(*)
from table t
group by (case when order_id != 0 then order_id end),
         (case when order_id = 0 then other_field end);

Note that I split the logic into two case statements. This just makes it easier if the types of the fields are not the same -- you don't have to deal with things like how to convert from one type to another.