CODEWITHSUNDEEP
mysqlselect
heysamhey
heysamhey

Reputation: 528

Sort MySQL Query based on SUM

I struggle to get the right query: There are two tables: articles and orders

I want to get a list from all the articles, but sorted by the times they were ordered. (Not only how many times the item was ordered, also including the amount)

orders: id, article_id, amount

articles: id, description

Example:

articles

1, apple
2, orange
3, lime

orders

1, 1, 5
2, 3, 1
3, 3, 2
4, 2, 1

Output should be:

1, apple,  5
3, lime,   3
2, orange, 1

Upvotes: 0

Views: 31

Answers (2)

juergen d
juergen d

Reputation: 204766

select a.key, a.description, sum(amount) as sum_amount
from articles a
left join orders o on o.article_key = a.key
group by a.key, a.description
order by sum_amount desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This is a basic join and group by query, two fundamentals of the SQL language. You should learn the language if you want to use databases effectively.

select a.`key`, count(o.id) as cnt, sum(amount)
from articles a left join
     orders o 
     on o.article_key = a.`key`
group by a.`key`
order by cnt desc;

Upvotes: 1

Related Questions