SEO

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 ;-)

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 ;-)
>

Thank you John!

This is definitely a push in the right direction. However, if I
understand your query correctly (and after a quick test to confirm),
this returns rows for the highest 40 percent *AND* less than 5
votes/scores. I would need the highest 40 percent *OR* items with less
than 5 votes.

Simply put, I'm trying to find a way for items with fewer than 5 votes
to be part of the result set even if their score is not in the top 40
percent.

There are several ways I can think of to do this but here's one.

Two queries.
One returns the top 40%, another less than 5 votes.
Union.

Wrap them with another query and use distinct to de-dupe.

Ordering them in any way could of course be tricky as you're comparing
apples n oranges.

andy (aon14@lycos.co.uk) writes:
> There are several ways I can think of to do this but here's one.
>
> Two queries.
> One returns the top 40%, another less than 5 votes.
> Union.
>
> Wrap them with another query and use distinct to de-dupe.

No need for a DISTINCT, as UNION implies distinct.

Here is a query drawn from John's repro that mayhe fits:

SELECT id, [Avg Score]
FROM (SELECT TOP 20 PERCENT [id], AVG(score*1.0) AS [Avg Score]
FROM #Scores
GROUP BY id
ORDER BY 2) AS x
UNION
SELECT id, AVG(score*1.0)
FROM #Scores
GROUP BY id
HAVING COUNT(*) < 5


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks guys.

I think you've answered my question. Created a query similar to
Erland's and it does exactly what I need it to. UNION did the trick.