Mysql IF in GROUP BY clause

Question

Is there any way to make an if in group by clause?

I have a query in which I want to group the result based on a column value

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

Edit: Sorry, I think I should add a more specific example

the columns below contain category id, stream and response

this is for the forum

those with zero values ​​mean they have no answer in them

if the answer is empty, I do not want to group it

the goal is to count responses and flows within a category

i did not set the value for the response as null, they are similar to the connection result

| 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 will be

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

Answer 1

In fact, you can 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 

Answer 2

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

Like this:

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

Perhaps not the right answer for your particular case, but convenient for other people who are looking for something like this.

Answer 3

If I understand that you are trying this path correctly:

 ( SELECT category, COUNT(thread) AS thread, COUNT(reply) AS reply FROM test WHERE reply IS NOT NULL GROUP BY category ) UNION ALL ( SELECT category, COUNT(thread) AS thread, reply FROM test WHERE reply IS NULL GROUP BY category ) ORDER BY category 

SQL Fiddle

Answer 4

MySQL will group all rows with fr = null into one, as if it were a normal value.

To solve your problem, I would make an additional choice of your table, in which the new column either gets the value fr (in case it is not null), or an extra value outside the normal volume fr, possibly a negative number. And then make an external selection group on this new virtual column.

 set @i := 0; select if(virtualFR<0,null,virtualFR) as fr, sum(fr_sum) from (select *, if(fr is null,@i: =@i-1 ,fr) as virtualFR from myTable) virtual group by virtualFR; 

sqlfiddle

Answer 5

You can simply do this:

 select min(id) id, sum(fr_sum) fr_sum, fr from mytable group by 3 

Answer 6

Try using the Group by

 select min(id), sum(fr_sum), fr from Table1 group by fr 

SQL Fiddle