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!
CREATE TABLE customer
fname VARCHAR (20)
CREATE TABLE sales_order
cust_id INTEGER, -- put in FK stuff yourself
INSERT INTO customer (fname) VALUES ('cust_1'), ('cust_2'), ('cust_3');
INSERT INTO sales_order (cust_id, sales_rep)
(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).
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!