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'
asked Dec 3, 2009 at 17:13
-
4You can't group by rows. You can group by columns thoughJoe Phillips– Joe Phillips2009-12-03 17:14:25 +00:00Commented Dec 3, 2009 at 17:14
-
1You may need to do a subquery instead of using multiple group by clauses.Adam Fowler– Adam Fowler2017-04-24 17:28:11 +00:00Commented Apr 24, 2017 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.ToolmakerSteve– ToolmakerSteve2019-03-06 13:48:55 +00:00Commented Mar 6, 2019 at 13:48
Add a comment
|
8 Answers
GROUP BY col1, col2, col3
answered Dec 3, 2009 at 17:14
Comments
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.
3 Comments
Will B.
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: sqlfiddle.com/#!9/d5f69/1 Note that row id: 2 is returned in both cases for 2,2 despite inverting the columns.
user2360831
One more testing. 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 columnuser2360831
Regarding
SUM using with GROUP BY. If GROUP BY only by one column, then SUMs all values of each distinct (different) the column value 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 sqlfiddle.com/#!9/1cbde2/1.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.
Comments
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
1 Comment
ToolmakerSteve
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.group by fV.tier_id, f.form_template_id
Comments
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;
1 Comment
ToolmakerSteve
This answer is worth noting, as it solves a somewhat different problem than the other answers.
Yes, you can group by multiple column in query
GROUP BY fV.tier_id, f.form_template_id
Comments
GROUP BY CONCAT(col1, '_', col2)
6 Comments
ypercubeᵀᴹ
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.
Abram
@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
ypercubeᵀᴹ
@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ᵀᴹ
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 ;Abram
@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.
|