Why do we use Group by 1 and Group by 1,2,3 in SQL query?

Question

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.

Answer 1

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:

• https://sqlblog.org/2009/10/06/bad-habits-to-kick-order-by-ordinal

Answer 2

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.

Answer 3

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

• Some people still teach it
• Some have column number in SQL diagrams. One line says: Sorts the result by the given column number, or by an expression. If the expression is a single parameter, then the value is interpreted as a column number. Negative column numbers reverse the sort order.
• Apache has deprecated its use because SQL Server has

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.

Answer 4

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.

Answer 5

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:

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

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).

Answer 6

This is worked for me. The code groups the rows up up to 5 groups.

SELECT
USR.UID,
USR.PROFILENAME,
(
    CASE 
    WHEN MOD(@curRow, 5) = 0 AND @curRow > 0 THEN
        @curRow := 0
    ELSE
        @curRow := @curRow + 1 
        /*@curRow := 1*/ /*AND @curCode := USR.UID*/
    END
) AS sort_by_total  
FROM
    SS_USR_USERS USR,
    (
        SELECT
            @curRow := 0,
            @curCode := ''
    ) rt
ORDER BY
    USR.PROFILENAME,
    USR.UID

The result will be as following