GROUP BY Works with Select columns not in GROUP BY

Question

why is this GROUP BY still working when the SELECTed columns are neither in the GROUP BY clause, nor aggregate function. DATABASE SCHEMA HERE

SELECT FirstName,
   LastName,
   City,
   Email,
   COUNT(I.CustomerId) AS Invoices
FROM Customers C INNER JOIN Invoices I
ON C.CustomerId = I.CustomerId
GROUP BY C.CustomerId

Good question. That invalid query is expected to raise an error. — jarlh, Feb 01 '22 at 08:28
Don't build applications with such queries, sooner or later they will become a problem. — jarlh, Feb 01 '22 at 08:29
This is the documented SQLite way of doing the thing *Each expression in the result-set is then evaluated once for each group of rows. If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single arbitrarily chosen row from within the group. If there is more than one non-aggregate expression in the result-set, then all such expressions are evaluated for the same row.* — Serg, Feb 01 '22 at 08:34
If you aren't happy with an arbitrary result, use sql standard GROUP BY. — Serg, Feb 01 '22 at 08:37
MySQL had a similar GROUP BY behavior versions ago. Today you can run MySQL in a compatibility mode to keep this behavior. — jarlh, Feb 01 '22 at 10:47

forpas · Accepted Answer · 2022-02-01T08:44:39.187

2

This syntax is allowed and documented in SQLite: Bare columns in an aggregate query.

The columns FirstName, LastName, City, Email are called bare columns.

Such columns get an arbitrary value with the exception of the case where one (and only this one) of MIN() or MAX() is used. In this case the values of the bare columns are taken from the row that contains then min or max aggregated value.

In any case be careful when you use this syntax because you would get unexpected results.

edited Feb 01 '22 at 08:44

answered Feb 01 '22 at 08:38

forpas

160,666
10
38
76

1

Read it through your provided link and it was helpful. Other dbms like, Postgresql and mysql will raise error, right. – what's wrong CSE Feb 01 '22 at 10:06

score 0 · Answer 2 · answered Feb 01 '22 at 09:25

Two things I want to talk about:

Group by will work if the column name exists in the table you are working on. In your query, you have inner join Customer table with Invoice table. From your schema, I can see in the Invoice table CustomerId column exists.

In SQL serve have to give all the column name that you selected plus your desired column name. What I mean by that your query should be like this.

 SELECT FirstName,
   LastName,
   City,
   Email,
   COUNT(I.CustomerId) AS Invoices
FROM Customers C INNER JOIN Invoices I
ON C.CustomerId = I.CustomerId
GROUP BY C.CustomerId, 
         LastName,
         City,
         Email

So, I think you are using MySQL that's why it's working.

It worked because I was using sqlite, But I was wondering why did it work, and above is the answer, Thanks though. — what's wrong CSE, Feb 01 '22 at 13:50

GROUP BY Works with Select columns not in GROUP BY

2 Answers2