MySQL group by only for 1 column in query

Question

I have a query which looks like this. The problem is that the entire query is grouped which is not desired.

select `col_1`, `col_2`, `col_3`
from `mytable` 
where (
     `col_4` LIKE '%abc%' 
  or `col_5` LIKE '%def%'  
  or `col_6` LIKE '%2020%'
)
GROUP BY col_1 # << this groups entire query

Instead, I am trying to aim for a particular column to be grouped like this.
I need to group col_1 for this specific condition col_6 LIKE '%zzz%'

select `col_1`, `col_2`, `col_3`
from `mytable` 
where (
     `col_4` LIKE '%abc%' 
  or `col_5` LIKE '%def%'  
  or (`col_6` LIKE '%2020%' GROUP BY col_1) #<< need this GROUP BY col_1
)

Heres an example of the output. Col_6 has 3 rows of 2020 and since 2 of those rows have the same value of "aaaa" in col_1, those 2 rows would need to be grouped. The value of col_3 for grouped items would just be the last item in the list.

+-------+-------+-------+-------+
| col_1 | col_2 | col_3 | col_6 |
+-------+-------+-------+-------+
| aaaa  | abc01 | red   | 2020  |
| aaaa  | abc01 | blu   | 2020  |
| aaaa  | abc01 | blu   | 2019  |
| aabb  | abc01 | grey  | 2020  |
| bbbb  | abc01 | purp  | 2019  |
| cccc  | abc01 | white | 2018  |
+-------+-------+-------+-------+

This query will have pagination and is intended for use in Laravel. Because of this UNION will not work because this interferes with Laravels pagination.

This would be the desired outcome

+-------+-------+-------+-------+
| col_1 | col_2 | col_3 | col_6 |
+-------+-------+-------+-------+
| aaaa  | abc01 | blu   | 2020  |
| aaaa  | abc01 | blu   | 2019  |
| aabb  | abc01 | grey  | 2020  |
| bbbb  | abc01 | purp  | 2019  |
| cccc  | abc01 | white | 2018  |
+-------+-------+-------+-------+

this groups the condition but I would need on top of this all the other items

select `col_1`, `col_2`, `col_3`
from `mytable` 
where (
     `col_4` LIKE '%abc%' 
  or `col_5` LIKE '%def%'  
  or `col_6` LIKE '%2020%'
)
GROUP BY col_1
HAVING `col_6` LIKE '%2020%'

Typically you should use GROUP BY when you are performing some kind of aggregation but you don't. Post sample data and expected results to clarify what you want. — forpas, Nov 21 '20 at 16:19
Help us help you - share some sample data and the result you're trying to get for it — Mureinik, Nov 21 '20 at 16:20
If these 2 rows are grouped together what will be the value in column col_3? — forpas, Nov 21 '20 at 17:04
@forpas I updated my question. The value will be insignificant for grouped items because the column will not be displayed on the front end. — t q, Nov 21 '20 at 17:11

score 2 · Answer 1 · answered Nov 21 '20 at 17:20

2

If the value of col_3 is insignificant then group by col_1, col_2, col_6:

select col_1, col_2, any_value(col_3), col_6
from mytable
where (
  <your conditions here>
)
group by col_1, col_2, col_6

answered Nov 21 '20 at 17:20

forpas

160,666
10
38
76

Good, but FWIW MySQL does not support the ANY_VALUE() aggregation function. Cf. https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html – Bill Karwin Nov 21 '20 at 17:25
@BillKarwin so what is this: https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_any-value – forpas Nov 21 '20 at 17:28
1

Mea culpa, I have not seen that function before, and it is not on the page for aggregate functions. You're right. – Bill Karwin Nov 21 '20 at 17:33
getting `Error Code: 1305. FUNCTION any_value does not exist ` – t q Nov 21 '20 at 19:42
@weber the function any_value() was introduced in Mysql 5.7 which now is in version 8.0. What is your version? – forpas Nov 21 '20 at 19:46
`mysql -v` `ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)` – t q Nov 21 '20 at 20:10

Bill Karwin · Answer 2 · 2020-11-21T19:02:39.270

It sounds like you want to group by both col_6 and col_1, which is fine.

select `col_1`, `col_2`, `col_3`, `col_6`
from `mytable` 
where (
     `col_4` LIKE '%abc%' 
  or `col_5` LIKE '%def%'  
  or `col_6` LIKE '%zzz%'
)
GROUP BY col_1, col_6

That way the result will have one row for each distinct pair of values in the two grouping columns.

Except this type of query becomes kind of ambiguous about the result for the non-grouping columns col_2 and col_3 in this example. From which row in the group should the value be used?

One method is to put the other columns in aggregating functions:

select `col_1`, MAX(`col_2`), MAX(`col_3`), `col_6`
from `mytable` 
where (
     `col_4` LIKE '%abc%' 
  or `col_5` LIKE '%def%'  
  or `col_6` LIKE '%zzz%'
)
GROUP BY col_1, col_6

But that's by value, not by row position. It could even take the values from different rows within the group, which might not be what you want.

If you intend the result row to be literally one of the rows in the group, so the other columns must come from the same row, then you have to have some way of resolving that. Thus you get into the various solutions for greatest-n-per-group problems, which are common on Stack Overflow.

Here's one I answered in the past, which got a lot of upvotes: Retrieving the last record in each group - MySQL

thank you but I need to group col_1 for this specific condition `col_6 LIKE '%zzz%' `, all the others will need the full result — t q, Nov 21 '20 at 19:00
Then it's really not clear what you're trying to do. I think you should show some more examples of data and the result you would want from that data. — Bill Karwin, Nov 21 '20 at 19:02

MySQL group by only for 1 column in query

2 Answers2