# GROUP BY with OR/AND clause?

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.

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.

• Thanks a lot, but when I try that. I get the following error: `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to, double_1 , double_2 , case when double_1 = double_2 ' at line 4 CREATE OR REPLACE VIEW vw_messages AS SELECT id , 'from' , to, double_1 , double_2 , case when double_1 = double_2 and to = 0 end flag_1 , case when double_1 = 0 and double_2 = 0 and to = 38 end flag_2 , case when double_1 = 0 and double_2 = 0 and 'from' = 38 end flag_3 FROM messages` – Mark Topper Apr 24 '13 at 11:50
• Sorry, I wrote the query straight from my brain compiler. It didn't warn me about the THEN clause missing... – listik Apr 24 '13 at 13:10
• Thanks a lot. I really need to look into the `CASE` clause, it seems useful. But sorry for the misunderstanding, as I actually wanted to do something else. I tried to explain it better in the question by editing it. Sorry and thanks a lot for your time. – Mark Topper Apr 24 '13 at 20:31
• Do not worry. I don't quite get what you want to get, though. If you do a group by using all columns but id, you would already get the rows grouped. If what you want is to get, separately, only the id's of rows that are "duplicated", you could create a view grouping the fields and adding the clause "HAVING COUNT(1)>1". Not sure if this is what you wanted; if that's the case, confirm it and I'll try to help! – listik Apr 26 '13 at 7:19