GROUP BY Works with Select columns not in GROUP BY-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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.