0

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 '17 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 '17 at 12:02
  • Here's the database.sql file in pastebin: pastebin.com/XrfTLbqZ @Vérace – shinzou Oct 7 '17 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 '17 at 12:38
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!

| improve this answer | |
  • 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 '17 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 '17 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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