[thelist] [MySQL] need help with a query

Joshua Olson joshua at waetech.com
Sat Mar 27 22:04:41 CST 2004


> -----Original Message-----
> From: Chris Johnston
> Sent: Saturday, March 27, 2004 8:49 PM
>
> What I am looking to generate is a table/view/query that would count all
> the occurances of a rating (1 - 5) and tell me how many of each appear
> for a single movie. So, for example, something like this:

Chris,

Here's something to get you started.  The key to this problem (if you take
the approach I'm going to suggest) is to get the data into a format where
the counts are all on one row.  Using some trickery we can get close to
where you want to be.  The rest of the processing can easily be done in the
middleware:

SELECT     idmovie, COUNT(*) AS c1, 0 AS c2, 0 AS c3, 0 AS c4, 0 AS c5
FROM         rating
WHERE     user_rating = 1
GROUP BY idmovie
UNION
SELECT     idmovie, 0 AS c1, COUNT(*) AS c2, 0 AS c3, 0 AS c4, 0 AS c5
FROM         rating
WHERE     user_rating = 2
GROUP BY idmovie
UNION
SELECT     idmovie, 0 AS c1, 0 AS c2, COUNT(*) AS c3, 0 AS c4, 0 AS c5
FROM         rating
WHERE     user_rating = 3
GROUP BY idmovie
UNION
SELECT     idmovie, 0 AS c1, 0 AS c2, 0 AS c3, COUNT(*) AS c4, 0 AS c5
FROM         rating
WHERE     user_rating = 4
GROUP BY idmovie
UNION
SELECT     idmovie, 0 AS c5, 0 AS c2, 0 AS c3, 0 AS c4, COUNT(*) AS c5
FROM         rating
WHERE     user_rating = 5
GROUP BY idmovie

What this gives you is up to 5 rows per idmovie.  Each row will have the
count of the records from the rating table in exactly one of the five "c"
columns.  You should be able to easily loop though the dataset and extract
what you need for your final presentation (hopefully).

Working within the constraints of MySQL, this is about as good as I can go.
If you were working with MSSQL I could get you to the solution you were
looking for about a half-dozen ways (some of which start with the solution
above).

Best of luck!  (this is a "tough" problem, btw)

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168




More information about the thelist mailing list