what does it mean when we use " group by 1" in an SQL Query

Question

I have come across a query where it is specified by

select concat(21*floor(diff/21), '-', 21*floor(diff/21) + 20) as `range`,     count(*) as 
`number of users` from new_table group by 1 order by diff;

here what exactly does group by 1 mean?

You can order with the position of the column I suposse it's the same with group by, It might be grouping by the first column on select list — Olvathar, Mar 20 '14 at 16:00
http://stackoverflow.com/questions/7392730/what-does-sql-group-by-clause-group-by-1-mean — adamr, Mar 20 '14 at 16:04

Fabian Bigler · Answer 1 · 2014-03-20T16:20:11.310

3

Assuming you have a Select:

SELECT name FROM employee GROUP BY 1;

No matter what, it will always group by the first column given in the select. In this case, the column 'name' is grouped.

So if we alternate the above statement to:

SELECT department FROM employee GROUP BY 1;

We now group the department, without having to change the '1' in the group by.

EDIT: (as requested by Stewart)

If we have the following Data in table 'employe':

-- name --
Walt
Walt
Brian
Barney

A simple select would deliver all rows above, whereas the 'group by 1' would result in one Walt-row:

output with group by:

-- name --
Walt
Brian
Barney

edited Mar 20 '14 at 16:20

answered Mar 20 '14 at 16:09

Fabian Bigler

10,403
6
47
70

But what does `group by` mean without an aggregate function? – Stewart Mar 20 '14 at 16:16
1

@Stewart it simply groups the column. give me one minute and I attach an example to my answer – Fabian Bigler Mar 20 '14 at 16:18
Looks a bit like `distinct` – Stewart Mar 20 '14 at 16:44
1

@Stewart I would suggest you to read up the definitions (just google it, really) and you will instantly see the difference. on Stack Overflow, there are already heaps of explanation between the two of them. ;-) – Fabian Bigler Mar 20 '14 at 17:25

score 0 · Answer 2 · answered Mar 20 '14 at 16:21

+1 to @FabianBigler for answering first, but I'll add this:

http://dev.mysql.com/doc/refman/5.6/en/select.html says:

Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1.

For what it's worth, this is non-standard SQL, so don't expect it to work on other brands of SQL database.

what does it mean when we use " group by 1" in an SQL Query

2 Answers2