Chris Chris - 6 months ago 11
SQL Question

Very basic SQL scipt

select Customers.cust_id, count(Orders.cust_id)
from Customers left outer join Orders
on Customers.cust_id=Orders.cust_id
group by Customers.cust_id


This correctly displays everything.

select Customers.cust_id, ***Customers.cust_name***, count(Orders.cust_id)
from Customers left outer join Orders
on Customers.cust_id=Orders.cust_id
group by Customers.cust_id


,,Your query does not include the specified expression 'cust_name' as port of an aggregate function."

Why is that? Each cust_id in Customers has a name in cust_name. Why do I get this error message?

Answer

When you use an aggregate function count() all other fields (that aren't used with an aggregate function) must appear in the Group By clause.


Here is my explanation as to why:

Aggregate functions operate across groups.

(That is, unless no groups or other fields are specified, in which case they operate across the whole recordset by default. For example, SELECT Sum(Salary) FROM Staff works.)

If you group by cust_id then it knows what to output, a count for each cust_id. But what would it do with the cust_name's? Which cust_name would it, or should it, display for each cust_id output? What if there are several cust_name's for a cust_id? It will only display one row for each cust_id, so what name should it display alongside it? It won't make the assumption that there is exactly one cust_name to correspond to one cust_id.

If there is one cust_name per cust_id then grouping by both will produce the same number of rows (as for cust_id alone) and provide consistent, and reliable, behaviour.