GROUP BY with OR/AND clause

group by

Lets say my database looks like this:

Table: messages 

+----+------+----+----------+----------+   
| id | from | to | double_1 | double_2 |   
+----+------+----+----------+----------+   
| 1  | 1    | 2  | 0        | 0        |  
| 2  | 1    | 0  | 1        | 2        |  
| 3  | 2    | 0  | 2        | 1        |  
| 4  | 2    | 1  | 0        | 0        |  
| 5  | 2    | 3  | 0        | 0        |  
| 6  | 2    | 0  | 1        | 3        |  
+----+------+----+----------+----------+

Then in my selection from the table, I want to GROUP BY the following:

  • group rows where double_1 and double_2 is the same, while to = 0
  • group rows where from is the same, while to = $id and double_1 = 0 and double_2 = 0
  • group rows where to is the same, while from = $id and double_1 = 0 and double_2 = 0

Anyone have any ideas how to do that?

EDIT: As it seems that i did make some people misunderstanding this. I will now explain better.
As I say the ways that I would like to GROUP BY, then when saying group rows where double_1 and double_2 is the same..., then its grouping the rows where double_1 and double_2 are the same. Like I have those two rows: 

+----+------+----+----------+----------+   
| id | from | to | double_1 | double_2 |   
+----+------+----+----------+----------+   
| 1  | 1    | 0  | 1        | 1        |  
| 2  | 1    | 0  | 2        | 2        |    
+----+------+----+----------+----------+

They should not be grouped, as ID 1's double_1 AND double_2 is not the same as ID 2's double_1 AND double_2. While this would group: 

+----+------+----+----------+----------+   
| id | from | to | double_1 | double_2 |   
+----+------+----+----------+----------+   
| 1  | 1    | 0  | 1        | 2        |  
| 2  | 1    | 0  | 1        | 2        |    
+----+------+----+----------+----------+

Im very sorry for the misunderstanding, hope that someone here are still willing to help me out.

Best Answer

You can generate a flag column for each group that you need to get:

 CREATE OR REPLACE VIEW vw_messages AS
 SELECT id
      , `from`
      , `to`
      , double_1
      , double_2
      , case when double_1 = double_2 
              and `to` = 0
             then 1
        else 0
        end flag_1
      , case when double_1 = 0
              and double_2 = 0
              and `to` = $id
             then 1
        else 0
        end flag_2
      , case when double_1 = 0
              and double_2 = 0
              and `from` = $id
             then 1
        else 0
        end flag_3
 FROM messages

This way you can later get the rows that accomplish either one or any combinations of groups.

Related Question