Is it possible to GROUP BY multiple columns using MySQL?

Question

Is it possible to GROUP BY more than one column in a MySQL SELECT query? For example:

GROUP BY fV.tier_id AND 'f.form_template_id'

What is this syntax version: http://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns — Ciro Santilli OurBigBook.com, Sep 07 '15 at 15:46
You may need to do a subquery instead of using multiple group by clauses. — Adam Fowler, Apr 24 '17 at 17:28
While most of the high-voted answers are essentially the same as each other (they show the correct syntax, and explain the effect of switching the order of the two columns), if your need is somewhat different, consider [Daniklad's answer](https://stackoverflow.com/a/42159638/199364). — ToolmakerSteve, Mar 06 '19 at 13:48

score 381 · Answer 1 · answered Dec 03 '09 at 17:14

381

GROUP BY col1, col2, col3

answered Dec 03 '09 at 17:14

Joe Phillips

49,743
32
103
159

score 190 · Answer 2 · edited Jun 23 '18 at 08:33

190

Yes, you can group by multiple columns. For example,

SELECT * FROM table
GROUP BY col1, col2

The results will first be grouped by col1, then by col2. In MySQL, column preference goes from left to right.

edited Jun 23 '18 at 08:33

Jordan Dodson

415
5
5

answered Jan 06 '14 at 04:43

php

4,307
1
24
13

17

Preference of left to right is applied to the ascending order of the groupings and not the column group preference. `GROUP BY` applies `col1+col2`. e.g. `col1 = 1, 2, 1, 2 | col2 = 1, 2, 3, 2` and running `GROUP BY col1,col2` would return `1,1|1,3|2,2` as opposed to `1,1|2,2` as suggested. Whereas `GROUP BY col2, col1` would change the ascending order of col2 returning. `1,1|2,2|1,3` Demo: http://sqlfiddle.com/#!9/d5f69/1 Note that row id: 2 is returned in both cases for `2,2` despite inverting the columns. – Will B. May 24 '16 at 17:35
One more testing. http://sqlfiddle.com/#!9/5c8763/2 Conclusion. At first mysql sorts by the first defined column (with `GROUP BY`). And if in the first defined column there are equal results, then only within the equal results sorts by the second defined column – user2360831 Jun 15 '19 at 06:17
Regarding `SUM` using with `GROUP BY`. If `GROUP BY` only by one column, then `SUM`s all values of each distinct (different) the column value http://sqlfiddle.com/#!9/1cbde2/2. If `GROUP BY` two columns. Then mysql at first checks if for the first column value there are different values in the second column. If yes, then mysql `SUM` each different value of the second column http://sqlfiddle.com/#!9/1cbde2/1. – user2360831 Jun 15 '19 at 06:37

score 30 · Answer 3 · answered Jun 02 '14 at 20:31

Yes, but what does grouping by more two columns mean? Well, it's the same as grouping by each unique pair per row. The order you list the columns changes the way the rows are sorted.

In your example, you would write

GROUP BY fV.tier_id, f.form_template_id

Meanwhile, the code

GROUP BY f.form_template_id, fV.tier_id

would give similar results, but sorted differently.

score 21 · Answer 4 · answered Dec 03 '09 at 17:15

21

group by fV.tier_id, f.form_template_id

answered Dec 03 '09 at 17:15

Trevor

6,659
5
35
68

score 21 · Answer 5 · answered Feb 10 '17 at 12:39

21

To use a simple example, I had a counter that needed to summarise unique IP addresses per visited page on a site. Which is basically grouping by pagename and then by IP. I solved it with a combination of DISTINCT and GROUP BY.

SELECT pagename, COUNT(DISTINCT ipaddress) AS visit_count FROM log_visitors GROUP BY pagename ORDER BY visit_count DESC;

answered Feb 10 '17 at 12:39

Daniklad

945
8
10

4

This answer is worth noting, as it solves a somewhat different problem than the other answers. – ToolmakerSteve Mar 06 '19 at 13:41

score 21 · Answer 6 · answered Jan 23 '18 at 12:17

21

If you prefer (I need to apply this) group by two columns at same time, I just saw this point:

SELECT CONCAT (col1, '_', col2) AS Group1 ... GROUP BY Group1

answered Jan 23 '18 at 12:17

Lucas Andrade

235
2
2

2

See ypercube's comments under lada's answer. Consider as an alternative: `SELECT CONCAT(col1, '_', col2) FROM GROUP BY col1, col2`. The results will usually *look* the same as this answer, but the internal execution is quite different. – ToolmakerSteve Mar 06 '19 at 13:25

score -7 · Answer 7 · answered Jul 23 '13 at 13:23

-7

GROUP BY CONCAT(col1, '_', col2)

answered Jul 23 '13 at 13:23

lada

355
2
1

47

I wonder how an answer with just one line of code, posted 4 years after the question had been answered gets 8 (eight!) upvotes. While also being incorrect and inefficient besides late and short. – ypercubeᵀᴹ Jul 20 '14 at 16:26
9

@ypercubeᵀᴹ why do you say it's incorrect? This is exactly what I was looking for, and a correct interpretation of "group by multiple columns". In fact, I don't know why this isn't the behavior of "group by col1, col2" like I'd expect – Abram Apr 28 '17 at 19:28
@NeverEndingQueue I don't what you have been looking for exactly but `GROUP BY CONCAT(col1, '_', col2)` has many downsides compared to ``GROUP BY col1, col2`. – ypercubeᵀᴹ May 17 '17 at 15:45
6

@Abram pinging you, so you see my reply to NeverEndingQueue. Downsides: it's less - much less -- efficient than `GROUP BY col1, col2`. It will give wrong results with some data. Say `col1, col2` have values: `('a_b', 'c')` in one row and `('a', 'b_c')` in another. This wrong answer, with GROUP BY CONCAT will aggregate the two rows in one. The correct answer will not. – ypercubeᵀᴹ May 17 '17 at 15:49
1

No one stops you from using the CONCAT expression in the SELECT list by the way, if you need it: `SELECT CONCAT(col1, '_', col2) ... FROM ... GROUP BY col1, col2 ;` – ypercubeᵀᴹ May 17 '17 at 15:51
1

@ypercubeᵀᴹ oops, stupidly I was thinking "group by foo, bar" behaved like "... group by foo union ... group by bar". It would be an unusual case indeed to GROUP BY CONCAT. – Abram May 18 '17 at 21:06

Is it possible to GROUP BY multiple columns using MySQL?

7 Answers7

Linked

Related