- 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"
Comments