Hard to explain, new at mysql

Question

Calculate each supplier's total sales quantity and get the sales person's name if the sales person supplies parts more than 1000 units in total.

Table info:

Supplier {s_num, s_name, status, city}

Spj {s_num, p_num, j_num, qty}

This is what I have:

SELECT s_name, SUM(qty) AS sum
FROM Supplier, Spj
WHERE Supplier.s_num = Spj.s_num
AND qty > 1000
GROUP BY s_name;

I think my error is in this line:

AND qty > 1000

maybe I am using the "GROUP BY" incorrectly...

No suppliers quantity is above 800, so I get a blank result.

I want to test this:

"sum of quantity for each supplier" > 1000

Replace `AND qty > 1000 GROUP BY s_name` with `GROUP BY s_name HAVING(qty > 1000)`. — DYZ, May 05 '17 at 02:56
Possible duplicate of [WHERE vs HAVING](http://stackoverflow.com/questions/2905292/where-vs-having) — DYZ, May 05 '17 at 02:58

score 0 · Accepted Answer · answered May 05 '17 at 02:56

0

Use HAVING to access aggregate results after a group by clause:

SELECT s_name, SUM(qty) AS sum
FROM Supplier, Spj
WHERE Supplier.s_num = Spj.s_num
GROUP BY s_name
HAVING qty > 1000;

See this post for more info on having vs where.

answered May 05 '17 at 02:56

Blue

1 Answers1