sql – Is it possible to GROUP BY multiple columns using MySQL?

The Question :

225 people think this question is useful

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'

The Question Comments :
  • You can’t group by rows. You can group by columns though
  • What is this syntax version: stackoverflow.com/questions/2421388/…
  • You may need to do a subquery instead of using multiple group by clauses.
  • 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.

The Answer 1

303 people think this answer is useful
GROUP BY col1, col2, col3

The Answer 2

128 people think this answer is useful

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.

The Answer 3

23 people think this answer is useful

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.

The Answer 4

17 people think this answer is useful
group by fV.tier_id, f.form_template_id

The Answer 5

14 people think this answer is useful

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;

The Answer 6

5 people think this answer is useful

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

The Answer 7

-3 people think this answer is useful
GROUP BY CONCAT(col1, '_', col2)

Add a Comment