In SQL queries, we do use Group by clause to apply aggregate functions.

  • But what is the purpose behind using numeric value instead of column name with Group by clause? For example: Group by 1.
  • 3
    Use order by 1 only when sitting at the mysql> prompt. In code, use ORDER BY id ASC. Note the case, explicit field name, and explicit ordering direction. – dotancohen Dec 20 '14 at 9:22

This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms.

The reasons people do it:

  • they're lazy - I don't know why people think their productivity is improved by writing terse code rather than typing for an extra 40 milliseconds to get much more literal code.

The reasons it's bad:

  • it's not self-documenting - someone is going to have to go parse the SELECT list to figure out the grouping. It would actually be a little more clear in SQL Server, which doesn't support cowboy who-knows-what-will-happen grouping like MySQL does.

  • it's brittle - someone comes in and changes the SELECT list because the business users wanted a different report output, and now your output is a mess. If you had used column names in the GROUP BY, order in the SELECT list would be irrelevant.

SQL Server supports ORDER BY [ordinal]; here are some parallel arguments against its use:

  • 9
    +1 for cowboy who-knows-what-will-happen grouping like MySQL does. Yee Haw !!! – RolandoMySQLDBA Dec 19 '14 at 13:27
  • +1, about why shouldn't we use numeric value in group by clause. – ursitesion Dec 22 '14 at 8:44

MySQL allows you to do GROUP BY with aliases (Problems with Column Aliases). This would be far better that doing GROUP BY with numbers.

Google has many examples of using it and why many have stopped using it.

To be honest with you, I haven't used column numbers for ORDER BY and GROUP BY since 1996 (I was doing Oracle PL/SQL Development at the time). Using column numbers is really for old-timers and backward compatibility allows such developers to use MySQL and other RDBMSs that still allow for it.

Consider below case:

+------------+--------------+-----------+
| date       | services     | downloads |
+------------+--------------+-----------+
| 2016-05-31 | Apps         |         1 |
| 2016-05-31 | Applications |         1 |
| 2016-05-31 | Applications |         1 |
| 2016-05-31 | Apps         |         1 |
| 2016-05-31 | Videos       |         1 |
| 2016-05-31 | Videos       |         1 |
| 2016-06-01 | Apps         |         3 |
| 2016-06-01 | Applications |         4 |
| 2016-06-01 | Videos       |         2 |
| 2016-06-01 | Apps         |         2 |
+------------+--------------+-----------+

You've to find out the number of downloads per service per day considering Apps and Applications as the same service. Grouping by date, services would result in Apps and Applications being considered separate services.

In that case, query would be:

 select date, services, sum(downloads) as downloads
 from test.zvijay_test
 group by date,services

And Output:

+------------+--------------+-----------+
| date       | services     | downloads |
+------------+--------------+-----------+
| 2016-05-31 | Applications |         2 |
| 2016-05-31 | Apps         |         2 |
| 2016-05-31 | Videos       |         2 |
| 2016-06-01 | Applications |         4 |
| 2016-06-01 | Apps         |         5 |
| 2016-06-01 | Videos       |         2 |
+------------+--------------+-----------+

But this is not what you want since Applications and Apps to be grouped is the requirement. So what can we do?

One way is to replace Apps with Applications using a CASE expression or the IF function and then grouping them over services as:

select 
  date,
  if(services='Apps','Applications',services) as services,
  sum(downloads) as downloads
from test.zvijay_test 
group by date,services

But this still groups services considering Apps and Applications as different services and gives the same output as previously:

+------------+--------------+-----------+
| date       | services     | downloads |
+------------+--------------+-----------+
| 2016-05-31 | Applications |         2 |
| 2016-05-31 | Applications |         2 |
| 2016-05-31 | Videos       |         2 |
| 2016-06-01 | Applications |         4 |
| 2016-06-01 | Applications |         5 |
| 2016-06-01 | Videos       |         2 |
+------------+--------------+-----------+

Grouping over a column number allows you to group data on an aliased column.

select
  date,
  if(services='Apps','Applications',services) as services,
  sum(downloads) as downloads
from test.zvijay_test
group by date,2;

And thus giving you desired output as below:

+------------+--------------+-----------+
| date       | services     | downloads |
+------------+--------------+-----------+
| 2016-05-31 | Applications |         4 |
| 2016-05-31 | Videos       |         2 |
| 2016-06-01 | Applications |         9 |
| 2016-06-01 | Videos       |         2 |
+------------+--------------+-----------+

I've read many times that this is a lazy way of writing queries or grouping over an aliased column does not work in MySQL, but this is the way of grouping over aliased columns.

This isn't the preferred way of writing queries, use it only when you really need to group over an aliased column.

  • "But this still groups services considering Apps and Applications as different services and gives the same output as previously". Wouldn't this be solved if you've chosen different (non-conflicting) name for the alias? – Daddy32 Jan 9 at 13:30

There is no valid reason to use it. It is simply a lazy shortcut specially designed to make it difficult for some hard-pressed developer to figure out your grouping or sorting later on or to allow the code to fail miserably when someone changes the column order. Be considerate of your fellow developers and don't do it.

SELECT dep_month,dep_day_of_week,dep_date,COUNT(*) AS flight_count FROM flights GROUP BY 1,2;

SELECT dep_month,dep_day_of_week,dep_date,COUNT(*) AS flight_count FROM flights GROUP BY 1,2,3;

Consider above queries: Group by 1 means to group by the first column and group by 1,2 means to group by the first and second column and group by 1,2,3 means to group by first second and third column. For eg:

group by 1,2

this image shows the first two columns grouped by 1,2 i.e., it is not considering the different values of dep_date to find the count(to calculate count all distinct combinations of first two columns is taken into consideration) whereas the second query results this group by 1,2,3

image. Here it is considering all the first three columns and there different values to find the count i.e., it is grouping by all the first three columns(to calculate count all distinct combinations of first three columns is taken into consideration).

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.