Meaning of Integer in GROUP BY clause

Question

I am testing and trying to understand and tweak a large query written by someone else, would like some help with the meaning of an integer in the GROUP BY section. It has the following structure:

SELECT
  column1,
  column2,
  SUM(column3),
  SUM(column4),
FROM
  table1 AS a
WHERE 
  column5 <> 3
GROUP BY
  column1,
  7,
  column2;

What does that 7 do? I can't really find it anywhere in the output, or see what effect it has had.

Possibly already answered [here](https://stackoverflow.com/questions/6380002/what-do-comma-separated-integers-in-a-group-by-statement-accomplish). — gautamaggarwal, Feb 23 '18 at 09:19
Does this query works? possible duplicate with [this](https://stackoverflow.com/questions/6380002/what-do-comma-separated-integers-in-a-group-by-statement-accomplish) — Alvaro Niño, Feb 23 '18 at 09:19
Possible duplicate of [What do comma-separated integers in a GROUP BY statement accomplish?](https://stackoverflow.com/questions/6380002/what-do-comma-separated-integers-in-a-group-by-statement-accomplish) — Alvaro Niño, Feb 23 '18 at 09:19

score 2 · Answer 1 · answered Feb 23 '18 at 09:19

2

When any integer value is used on Group by, it means to group by the 7th column. Same can be done with ORDER BY

I assumed that you have provided the code for sample in your actual code you must have 7 or more than 7 columns in SELECT clause

answered Feb 23 '18 at 09:19

Mittal Patel

2,732
14
23

score 2 · Accepted Answer · answered Feb 23 '18 at 09:21

2

The documentation of the SELECT statement explains:

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.

GROUP BY 7 in your query is not a valid column position because the SELECT clause contains only 4 expressions.

answered Feb 23 '18 at 09:21

axiac

68,258
9
99
134

Ok thanks, well as I did not know what it meant, only wrote out 4 things in the select statement. The real query contains 14 things in the select statement, and I observe if I change the 7 to a 14, it still works, but a 15 causes an error. OK, **columns selected for output** which can be not just the columns but `CONCAT(column6, column7)` for example, not columns in the original table, but **selected for output** – cardamom Feb 23 '18 at 09:27
1

That's because there is no "the 15th column" in the `SELECT` clause. – axiac Feb 23 '18 at 09:29

score 0 · Answer 3 · answered Feb 23 '18 at 19:24

0

The integer values used in GROUP BY and ORDER BY are the position of the columns starting from 1 ( sorry array's counting system ;) ).

You can look more of it on the official documentation here.

answered Feb 23 '18 at 19:24

stackFan

1,528
15
22

Meaning of Integer in GROUP BY clause

3 Answers3