Column must appear in the GROUP BY clause or be used in an aggregate function?

user_14954401 0
Viewed 2246

enter image description here

The question is We want to sum the orders that are above $700.

Here is my code so far

SELECT sum(t2.amount), t1.name 
FROM Salesperson t1
INNER JOIN Orders t2 ON t1.ID  =  t2. salesperson_id
WHERE t2.amount >= 700

However I get an error

Column "t1.name" must appear in the GROUP BY clause or aggregate function

Why is that?

sql
2 Answers

You need to specify the fields that will be used to aggregate (group) the results, in this case the salesperson name.

SELECT sum(t2.amount), t1.name 
FROM Salesperson t1
INNER JOIN Orders t2 ON t1.ID  =  t2.salesperson_id
WHERE t2.amount >= 700
GROUP BY t1.name
user_5099400

If you want just sum the amount order I think this is will work :

SELECT sum(t2.amount), t1.name 
FROM Salesperson t1
INNER JOIN Orders t2 ON t1.ID  =  t2. salesperson_id
GROUP BY(salesperson_id)
HAVING t2.amount >= 700;

The error because some salesperson_id make more than one order so you need to group them by its id to make each rows represent the total amount order for each salesperson. I hope this answer useful for you and fix your problem.

user_150570990
Related