About "having" in SQL

Question

I have the problem:

Find all the customers that have an order that was handled by employee number 129 and count all the orders they made.

What I tried:

select c.id, c.fname, count(*)
from customer c inner join sales_order so 
on c.id = so.cust_id 
group by c.id, c.fname 
having so.sales_rep = 129;

But it isn't correct and the answer is:

select c.id, c.fname, count(*)
from customer c inner join sales_order so 
where c.id = so.cust_id and c.id in (
select s.cust_id
from sales_order s 
where s.sales_rep = 129)
group by c.id, c.fname;

Isn't the keyword having in SQL like a condition that runs on each group? Then why won't all the groups that have so.sales_rep = 129 come up?

These are the outputs: https://pastebin.com/J9LD1L9L

Answer 1

To answer this, I did the following. I used PostgreSQL 9.6 on db-fiddle (a very useful site for putting up code to help ask good questions!). All other servers should act the same way (unless you're using MySQL without ONLY_FULL_GROUP_BY when you can get, ahem..., unpredictable results. You should never do this!). p.s. SQLite actually models itself on PostgreSQL, so any results from one are normally applicable to the other!

CREATEed my TABLEs:

CREATE TABLE customer
(
  id SERIAL,
  fname VARCHAR (20)
);

CREATE TABLE sales_order
(
  order_id SERIAL,
  cust_id INTEGER,  -- put in FK stuff yourself
  sales_rep INTEGER 
);

Input data:

INSERT INTO customer (fname) VALUES ('cust_1'), ('cust_2'), ('cust_3');
INSERT INTO sales_order (cust_id, sales_rep)
VALUES
(1, 111), (1, 121), (1, 129), (2, 111), (2, 121), (3, 111), (3, 129), (3, 129);

Running your original query (see db-fiddle here), gives the error

Query Error: error: column "so.sales_rep" must appear in the GROUP BY clause or be used in an aggregate function.

If you modify your query slightly, as shown below (db-fiddle)

select c.id, c.fname, count(*)
from customer c inner join sales_order so 
on c.id = so.cust_id 
group by c.id, c.fname, so.sales_rep  -- Note addition of so.sales_rep!
having so.sales_rep = 129;

you get the correct answer

id  fname   count
3   cust_3  2
1   cust_1  1

I personally would find it more helpful to include the sales rep's name (or join it to a sales_rep table to include his/her name (db-fiddle).

The HAVING clause is looking for so.sales_rep in its processing, but can't find it in your original - adding it in the GROUP BY gives the HAVING the information it needs to only produce results for sales_rep no. 129!