GROUP BY on certain condition

Question

I'm having some MySQL difficulties in the GROUP BY section of my query. There is a column I would like to use in the GROUP BY, but that should only be used for a certain condition that depends on a value in a row. Let's say I have this table:

mysql> SELECT * FROM test_table;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 2 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 6 | 3 | 1 |
| 7 | 2 | 2 |
+---+---+---+
7 rows in set (0.00 sec)

Now I would like to retrieve all the rows, where it should only use GROUP BY on column c, when b === 1. That means I would like to get 6 rows back, because the rows number 2 and 3 should be grouped. So I came up with the following query, which doesn't give me the expected result.

mysql> SELECT * FROM test_table GROUP BY CASE WHEN b = 1 THEN c END;
+---+---+---+
| a | b | c |
+---+---+---+
| 4 | 2 | 2 |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
+---+---+---+
3 rows in set (0.00 sec)

I'm probably missing something within the combination of the CASE statement and the GROUP BY and I hope any of you could help me out a bit.

"That means I would like to get 5 rows back, because the rows number 2 and 3 should be grouped. " So the row with `| 3 | 1 | 2 |` should be 'missing' — LaVomit, Jan 27 '15 at 10:13
Sure, I saw that. But it doesn't mean anything. What values do you expect to get from that group? The purpose of `GROUP BY` is not to ignore rows but to compute aggregate values from groups of rows. If you want to ignore the row with `a == 3` you can use `DISTINCT`. — axiac, Jan 27 '15 at 10:13
What is the selection criteria when there are two or more rows having `b == 1` and the same value on column `c`? — axiac, Jan 27 '15 at 11:06

Raging Bull · Accepted Answer · 2015-01-27T10:29:42.947

1

You can use a simple GROUP BY clause with columns b and c separated by comma:

SELECT * 
FROM test_table 
GROUP BY CASE WHEN b=1 THEN c ELSE a END;

Result:

See result in SQL Fiddle.

edited Jan 27 '15 at 10:29

answered Jan 27 '15 at 10:13

Raging Bull

18,113
13
47
53

1

This query can ignore the row `| 2 | 1 | 2 |` as well (and return `| 3 | 1 | 2 |` instead). `SELECT`ing a column (`a` in this case) that is neither include in `GROUP BY` nor functionally dependent on those included is an error in standard `SQL`. It is accepted by MySQL but the result is **undefined behaviour**. It is [explicitly specified](http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) in the documentation: *"the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate"* – axiac Jan 27 '15 at 10:18
That would not do the trick. If there would be another row with b = 2 and c = 2, that would be grouped with the row where a = 4. – LaVomit Jan 27 '15 at 10:19
I updated my question / example table. Of course in my case, this involves several `JOIN`s on different tables, which would make the real query a bit more difficult. But I would like to understand a bit how it works. The explanation on MySQL about `CASE` and `GROUP BY` are insufficient to me. – LaVomit Jan 27 '15 at 10:23
@LaVomit: Updated my answer. Try it now. – Raging Bull Jan 27 '15 at 10:40
1

Thank you Raging Bull. Although in my case a `GROUP BY` on column a is dependent on another `CASE`, I think this would be the best approach. I got it working now the way I wanted. – LaVomit Jan 27 '15 at 10:54
@LaVomit: check this [SQL fiddle](http://sqlfiddle.com/#!2/b1cb26/1). It's the same data in the table, the same query, but it returns a different result set. – axiac Jan 27 '15 at 11:08
@axiac: Please refer [**this**](http://stackoverflow.com/questions/23888082/which-record-will-group-by-choose-in-sql) – Raging Bull Jan 27 '15 at 11:12

score 0 · Answer 2 · answered Jan 27 '15 at 10:21

0

you may need to do something on these lines depending on your required output

SELECT c, count(*) FROM test_table where b=1 GROUP BY c
UNION 
SELECT c, count(*) FROM test_table where b<>1

answered Jan 27 '15 at 10:21

Jayvee

10,270
3
26
39

That gives me the expected result indeed. Although I would like to seek an alternative to the use of `UNION`. As this refers to very large tables, it will take a while to execute. A `UNION` would make the query about twice as long (with all the `JOIN`s et cetera). – LaVomit Jan 27 '15 at 10:25
what column(s) are you aggregating?, I just used a group by c and count of rows.as an example. Could you please post your required output? – Jayvee Jan 27 '15 at 10:30

GROUP BY on certain condition

2 Answers2