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

  • Why can't you use 'where so.sales_rep=129' in the first query?
    – Tom V
    Oct 7, 2017 at 11:57
  • We want to count all of their orders, including orders that were made by other sales reps from 129. @TomV
    – shinzou
    Oct 7, 2017 at 12:02
  • Here's the database.sql file in pastebin: pastebin.com/XrfTLbqZ @Vérace
    – shinzou
    Oct 7, 2017 at 12:27
  • Your code "sample" is far too big to run on db-fiddle - anyway, the principles are the same - you should be able to run the code I've shown you on your local server? Also, see what I wrote (in edit) about SQLite being modelled on PostgreSQL.
    – Vérace
    Oct 7, 2017 at 12:38

1 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!


  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)
(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!

  • Why does the so.sales_rep have to be in the group by and why does it make it behave differently? The count is not correct but the names are correct now: i.imgur.com/IEWHvYh.png (I added a link to the db in the comments to the question)
    – shinzou
    Oct 7, 2017 at 12:25
  • The results are correct using the data I used in db-fiddle. I don't have the time to write out your raw text into SQL statements by hand - this is why it's good to use db-fiddle! so.sales_rep has to be in the query because HAVING can only act upon fields that are in the GROUP BY - it "doesn't know" about other stuff. It says, "Right, I've got this query above me returning results - now, I need to get those results for which the sales_rep = 129 - but hey, I've no way of knowing this, so I'll throw an error until the programmer gives me the information I need to do my job!"
    – Vérace
    Oct 7, 2017 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.