• Column Restriction

If you choose to also include expressions that reference columns but do not include an aggregate function, you must list all columns you use this way in the GROUP BY clause.


One of the most common mistakes is to assume that you can reference columns in nonaggregate expressions as long as the columns come from unique rows.

 

 

For example:

Table

  Student


Colunms:

  ID                Numeric   Primary key

  Name          String       Not Null

  Age             Int            Not Null

  Class          String       Not Null

  Score          Int            Not Null

So if we want get the average score and total class selected for each student include ID, name


Failed query:

select ID, Name, Count(*) as NumOfClass, Sum(score)/numOfClass

from Student

group by ID


Correct query:

select ID, Name, Count(*) as NumOfClass, Sum(score)/numOfClass

from Student

group by ID, Name


More reasonable query:

select Name, Count(*) as NumOfClass, Sum(score)/numOfClass

from Student

group by Name


  • Grouping on Expressions

One of the most common mistakes is to attempt to group on the expression you create in the SELECT clause rather than on the individual columns. Remember that the GROUP BY clause must refer to columns created by the FROM and WHERE clauses. It cannot use an expression you create in your SELECT clause.

 

For example:

Wrong Sql:

SELECT Customers.CustLastName || ', ' ||
Customers.CustFirstName AS CustomerFullName,
Customers.CustStreetAddress || ', ' ||
Customers.CustCity || ', ' ||
Customers.CustState || ' ' ||
Customers.CustZip AS CustomerFullAddress
MAX(Engagements.StartDate) AS LatestDate,
SUM(Engagements.ContractPrice)
AS TotalContractPrice
FROM Customers
INNER JOIN Engagements
ON Customers.CustomerID =
Engagements.CustomerID
WHERE Customers.CustState ='WA'
GROUP BY CustomerFullName,
CustomerFullAddress


Correct Sql:

SELECT CE.CustomerFullName,
CE.CustomerFullAddress,
MAX(CE.StartDate) AS LatestDate,
SUM(CE.ContractPrice)
AS TotalContractPrice
FROM
(SELECT Customers.CustLastName || ', ' ||
Customers.CustFirstName AS CustomerFullName,
Customers.CustStreetAddress || ', ' ||
Customers.CustCity || ', ' ||
Customers.CustState || ' ' ||
Customers.CustZip AS CustomerFullAddress,
Engagements.StartDate,
Engagements.ContractPrice
FROM Customers
INNER JOIN Engagements
ON Customers.CustomerID =
Engagements.CustomerID
WHERE Customers.CustState ='WA')
AS CE
GROUP BY CE.CustomerFullName,
CE.CustomerFullAddress


It referenced from "SQL Queries for Mere Mortals, Second Edition"