SQL Server Group By which counts occurrences of a score

Question

Hi,

This might be a bit difficult to explain but I have two columns in my SQL server database which I have simplified...

Items
ID
itemName
voteCount
score

Votes
itemID
score

So, basically I am storing every vote that is placed in one table, but also a count of the number of votes for each item in the item table along with it's average score (out of 10) (which I know is duplicating data but it makes things easier).

Anyway, I want to create a SQL query which finds the 2 items which have the lowest score. This would be easy you would think as you'd just do this...

SELECT TOP 2 itemName FROM Items ORDER BY score ASC;

However, the client has added the following complication.

When 2 or more items have the same score then the item with the highest number of 10/10 votes would be placed above. If 2 or more items have the same score AND the same number of 10/10 votes then it would rank the item with the most 9/10 votes above the others and so on, right down to the number of 0/10 votes if everything else is equal.

So, the challenge is to rank all the items by these criteria then pick off the bottom 2. I have tried every combination of grouping, aggregating and "sub-querying" to work this out but I think I need the help of somebody much cleverer than me.

Any help would really be appreciated.

Clarification

The average score for an item is stored in the item table and the score cast against each vote is kept in the votes table. Initially we need to rank by the average score (I.score) and where 2 items have the same score we need to start counting the number of 10/10's in the votes linked to that item (v.score).

So, we might have an item called "t-shirt" which has an average score of 5/10. This comes from 6 votes with the following scores 5,5,5,5,5,5.

The next item is called "Ferrari" and also has an average score of 5/10, but this item only has 4 votes with the following scores 6,5,5,4

Clearly, the ferrari should win because the sql would see that it has no 10's, no 9's, no 8's, not 7's, but it does have a vote of 6 which trumps the t-shirt.

Answer 1

SELECT TOP 2 i.itemName 
FROM Items i
left outer join (
    select ItemID, 
        sum(case when score = 10 then 1 end) as Score10,
        sum(case when score = 9 then 1 end) as Score9,
        sum(case when score = 8 then 1 end) as Score8,
        sum(case when score = 7 then 1 end) as Score7,
        sum(case when score = 6 then 1 end) as Score6,
        sum(case when score = 5 then 1 end) as Score5,
        sum(case when score = 4 then 1 end) as Score4,
        sum(case when score = 3 then 1 end) as Score3,
        sum(case when score = 2 then 1 end) as Score2,
        sum(case when score = 1 then 1 end) as Score1
    from Votes
    group by ItemID
) v on i.ID = v.ItemID
ORDER BY i.score, 
    v.Score10,
    v.Score9,
    v.Score8,
    v.Score7,
    v.Score6,
    v.Score5,
    v.Score4,
    v.Score3,
    v.Score2,
    v.Score1