Hi

Use TOP to get the highest percentages and a HAVING clause with a count will
give you how many items were added in the SUM. e.g

CREATE TABLE #Scores ( [id] int, score int )

INSERT INTO #Scores ( [id], score )
SELECT 1, 8
UNION ALL SELECT 1, 9
UNION ALL SELECT 1, 10
UNION ALL SELECT 1, 7
UNION ALL SELECT 2, 4
UNION ALL SELECT 2, 6
UNION ALL SELECT 2, 5
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 4, 4
UNION ALL SELECT 4, 4
UNION ALL SELECT 4, 4
UNION ALL SELECT 5, 7
UNION ALL SELECT 5, 7
UNION ALL SELECT 5, 7
UNION ALL SELECT 6, 6
UNION ALL SELECT 6, 6
UNION ALL SELECT 6, 6

-- Not ordered
SELECT [id], SUM(score) AS [Total Score]
FROM #scores
GROUP BY [id]
HAVING COUNT(*) < 5

-- In order highest first
SELECT [id], SUM(score) AS [Total Score]
FROM #scores
GROUP BY [id]
HAVING COUNT(*) < 5
ORDER BY [Total Score] DESC

-- Not ordered therefore don't get highest values
SELECT TOP 40 PERCENT [id], SUM(score) AS [Total Score]
FROM #scores
GROUP BY [id]
HAVING COUNT(*) < 5

-- Ordered so get highest 40 PERCENT
SELECT TOP 40 PERCENT [id], SUM(score) AS [Total Score]
FROM #scores
GROUP BY [id]
HAVING COUNT(*) < 5
ORDER BY [Total Score] DESC

DROP TABLE #Scores

Check out the information in Books online for more details

John

<andreas.muller@gmail.com> wrote in message
news:1116040912.686078.111690@f14g2000cwb.googlegr oups.com...
> Hello everyone,
>
> I'm trying to solve this problem but can't seem to figure out how to
> start. I would like to create a rating system where people can vote
> (1-5 stars) on randomly displayed items. The randomly displayed items
> should either have very high ratings OR a very low number of ratings.
> For example, only return items in the top 20th percentile *OR* items
> with fewer than 5 votes.
>
> The question is, how would I write an SQL query to return such a
> result? Is it even possible? Should this be handled by my application
> rather than the database?
>
> For simplicity, let's assume I have the following table:
>
> tbl_items
> -----------------
> item_id
> item_name
> avg_rating
> num_votes
> -----------------
>
> Any help or pointers in the right direction would be greatly
> appreciated. My apologies in advance if the solution is obvious and I
> am clearly missing the point ;-)
>