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 ;-) 5 4116
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
<an************@gmail.com> wrote in message
news:11**********************@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 (ao***@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, es****@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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: YoBro |
last post by:
Hi,
I have a database that stores in a field the number of views for a product.
I want to write a query that gets the two highest views, so I can then
output the result.
I have this so far,...
|
by: Brian Newsham |
last post by:
------=_NextPart_000_004F_01C352B1.E5B8FA20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I'm working on a PHP based website that loads...
|
by: Dom |
last post by:
I need to run a query based on a query in Access. The second query has
a number of conditions which all work well, but there is one more
contition I need to set to make it run properly.
the...
|
by: Serious_Practitioner |
last post by:
Good day, and thank you in advance for any assistance you can provide.
I have a table in an Access 2000 .mdb file, and I've run into something odd
and insolvable, at least for me. The database is...
|
by: johnk |
last post by:
I have a table of items, with revision numbers. I need to extract the
items with highest revision number. The items may be listed several
times and I don't know what the highest revision number...
|
by: uspensky |
last post by:
I have a table (cars) with 3 fields:
VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
|
by: Wired Hosting News |
last post by:
I tried to be breif and give a scenario so as not to be overlooked because
it was soooo long. Let me give you real world.
I am a manufacturer of goods and produce 11 items that are distributed...
|
by: fperri |
last post by:
Hello,
I have a query that creates a table of duplicates in my database. For one set of duplicates (three records), all the fields & values are the same except for the values in one field. This...
|
by: Tarscher |
last post by:
Hi all,
I have events containing attendees (events has many attendees). The
attendee table tells whether a user will attend the event or not. I
want to build a query that returns all the...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |