For each item, find COUNT of item with lower price

I have a table Items(ID, Name, Price).

For every item in Items, I‘d like to receive an output of the form: (ID, Name, Count), where Count is the number of items in Items which are cheaper in price.

I have tried running a subquery with the same table but found no solution. It seems so simple, but I‘m a bit stuck.

by William Bomb in StackOverflow on July 05, 2022.

Answer

SELECT t1.ID, t1.Name, t1.Price, COUNT(t2.id)
FROM Items t1
LEFT JOIN Items t2 ON t1.price > t2.price
GROUP BY t1.ID, t1.Name, t1.Price

I add Price column to the output because you have not told that the presence of the same item name with different prices is not possible.

by Akina on July 05, 2022.