What is GROUP BY 1 in MySQL and what's the advantage?

Question

I was viewing a question on stackoverflow and came across the following query. I was wondering what the GROUP BY 1 was for.

SELECT
date(orderdate),
COUNT(id) AS num_orders
FROM orders
GROUP BY 1
ORDER BY orderdate DESC

I know this query will generate a list of orders per day ordered from high to low. I would just like to understand the GROUP BY

I mean either I should use column name or column position. Which method is preferable and why?

Possible duplicate of [What does SQL clause "GROUP BY 1" mean?](http://stackoverflow.com/questions/7392730/what-does-sql-clause-group-by-1-mean) — Giorgi Nakeuri, Nov 24 '15 at 07:50
@GiorgiNakeuri I would also like to know the advantages of using this terminology. Does it still make my question a duplicate? — Peter, Nov 24 '15 at 07:56
Hope this will answer your question: [Why do we use Group by 1 and Group by 1,2,3 in SQL query?](http://dba.stackexchange.com/questions/86609/why-do-we-use-group-by-1-and-group-by-1-2-3-in-sql-query) — Muhammad Muazzam, Nov 24 '15 at 08:47

score 2 · Accepted Answer · answered Nov 24 '15 at 07:52

2

GROUP BY 1 means, grouping of data on basis of first column. You can apply with ORDER BY too.

answered Nov 24 '15 at 07:52

Simhachalam Gulla

score 2 · Answer 2 · answered Nov 24 '15 at 07:57

2

SELECT date(orderdate),COUNT(id) AS num_orders
          ^^^^          ^^^^
          1            2
FROM orders
GROUP BY 1

In above query GROUP BY 1 refers to the first column in select statement which is date(orderdate).

answered Nov 24 '15 at 07:57

Abhishek Sharma

2 Answers2