ERROR: SQL showing total quantity sold from product table and using RIGHT JOIN problem

Question

Show total quantity sold from product table. Display product code, description, unit, quantity. Use RIGHT JOIN. Sort according to the most product sold.

this is my work. What is the problem of this

SELECT  sd.prodCode, SUM(sd.quantity) "total_quantity", p.prodCode, p.description, p.unit
FROM salesDetail sd
RIGHT JOIN product p 
ON p.prodCode = sd.prodCode
GROUP BY sd.prodCode
ORDER BY SUM(sd.quantity);

Error message An expression starting with "UNIT" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified..

By the way `sd.prodCode` will be null for every `product` with non-existent `salesDetail`. If you flip it round and write this as a left join, you will see why (and that is why no-one uses right joins) — Charlieface, Jan 31 '21 at 06:03

score 0 · Accepted Answer · answered Jan 31 '21 at 06:01

As the error suggest:

"UNIT" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause

You need to group the data by every field that you use on the SELECT statement that is not an aggregate function (like SUM(), AVG(), COUNT()).

Like this:

SELECT  sd.prodCode, p.prodCode, p.description, p.unit, SUM(sd.quantity) "total_quantity"
FROM salesDetail sd
RIGHT JOIN product p 
ON p.prodCode = sd.prodCode
GROUP BY sd.prodCode, p.prodCode, p.description, p.unit
ORDER BY SUM(sd.quantity);

thank you! so the problem is i need to GROUP BY also the group — Raizenjr., Jan 31 '21 at 06:38

ERROR: SQL showing total quantity sold from product table and using RIGHT JOIN problem

1 Answers1