[SOLVED] GROUP BY Works with Select columns not in GROUP BY

GROUP BY Works with Select columns not in GROUP BY

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

Solution

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.