[OT] mysql query help for listing multiple ratings on multiple questions for multiple items

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[OT] mysql query help for listing multiple ratings on multiple questions for multiple items

Tami Williams-3
Thanks in advance for any help.

I need to have final output that looks like this (after a user has  
searched for some item criteria, as in find me all items that are in  
zip code "30341":

Item Review
ItemA Question1 Averaged 5 Stars out of 41 responses  - # of people  
who answered this question for this item
                Question2 Averaged 3 Stars out of 100 responses - # of people who  
answered this question for this item
                Question3 Averaged 2 Stars out of 15 responses

ItemB Question1 Averaged 2 Stars out of 55 responses
                Question2 Averaged 4 Stars out of 10 responses
                Question3 Averaged 3 Stars out of 90 responses

Note: there can be 100s of "items" in the found set.  Each item will  
have 5 or so questions, each question will have some # of total  
responses,
and I need to calculate the # of people who responded x# stars (ex:the  
# of people who responded 4 stars,
the # of people who responded 3 stars, etc).  Sometimes there will be  
no one who responded 5/4/3/2/1 stars and so I won't display a line for  
that number of stars.

I've thinking of having an item table joined to a 'stars' table by  
'itemID'.


I can calculate the # people who responded x# stars for a SINGLE  
question for a SINGLE item using the data and mysql query below.


The problem is using 1 query to :

a. give me the output above - for all questions, all items in my found  
set  - can 1 query do all this?  what would that look like?

OR

b.  for every item record in my found set, a MySQL query that grabs  
all of that item's answers and calculates the # people who responded  
x# stars for every question and the total # of responses per question.


--------------------------------------------------------------------------------------

NOTE: test data below, you can ignore the reviewID.


CREATE TABLE `stars` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `reviewID` int(10) unsigned DEFAULT NULL,
   `itemID` int(10) unsigned DEFAULT NULL,
   `questionID` int(10) unsigned DEFAULT NULL,
   `questionValue` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

ALTER TABLE stars ADD INDEX(reviewID);
ALTER TABLE stars ADD INDEX(itemID);
ALTER TABLE stars ADD INDEX(questionID);
ALTER TABLE stars ADD INDEX(questionValue);


With this data:

note: questionValue = # stars

mysql> select * from stars;
+----+----------+------------+------------+---------------+
| id | reviewID | itemID | questionID | questionValue |
+----+----------+------------+------------+---------------+
|  3 |        1 |          1 |          2 | 5             |
|  4 |        2 |          1 |          1 | 5             |
|  5 |        1 |          1 |          1 | 5             |
|  6 |        2 |          1 |          1 | 5             |
|  7 |        1 |          1 |          1 | 5             |
|  8 |        2 |          1 |          2 | 5             |
|  9 |        2 |          1 |          2 | 5             |
| 10 |        1 |          1 |          2 | 3             |
| 11 |        2 |          1 |          2 | 4             |
| 12 |        2 |          1 |          2 | 3             |
| 13 |        1 |          1 |          1 | 4             |
| 14 |        2 |          1 |          1 | 3             |
| 15 |        1 |          1 |          1 | 2             |
| 16 |        1 |          1 |          1 | 3             |
| 17 |        1 |          1 |          1 | 4             |
| 19 |        3 |          2 |          2 | 5             |
| 20 |        3 |          2 |          2 | 3             |
| 21 |        3 |          2 |          2 | 3             |
+----+----------+------------+------------+---------------+



And this query:
note: totalNbrAnswersForThisQ = # responses, as if 5 people said 5  
stars out of 200 responses.

SET @totalNbrAnswersForThisQ=(SELECT
Count(*) AS NbrAnswersToThisQ
FROM  stars
WHERE questionID=1 and itemID = 1);

SELECT
questionID,
questionValue,
Count(questionValue) AS NbrAnswersForThisValue,
@totalNbrAnswersForThisQ as totalNbrAnswersForThisQ,
ROUND((COUNT(*) /@totalNbrAnswersForThisQ)*100,2) AS percentage
FROM
stars
WHERE itemID = 1
and questionID = 1
GROUP BY
questionID, questionValue;


Output:

| questionID | questionValue | NbrAnswersForThisValue |  
totalNbrAnswersForThisQ | percentage |
+------------+---------------+------------------------
+-------------------------+------------+
|          1 | 2             |                      1  
|                       9 |      11.11 | for questionID 1, 1  
person said 2 stars out of 9 resopnses to question 1 11.1%
|          1 | 3             |                      2  
|                       9 |      22.22 | for questionID 1, 2  
people said 3 stars out of 9 resopnses to question 1 22.2%
|          1 | 4             |                      2  
|                       9 |      22.22 | for questionID 1, 2  
people said 4 stars out of 9 resopnses to question 1 22.2%
|          1 | 5             |                      4  
|                       9 |      44.44 | for questionID 1, 4  
people said 5 stars out of 9 resopnses to question 1 44.4%
+------------+---------------+------------------------
+-------------------------+------------+





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"It's better to burn out than to fade away."

Tami Williams
Creative Computing
Let us help you make frustrating, costly, and inefficient processes  
more efficient, less costly and scalable.
Lasso, MySQL and FileMaker specialists.

Tel: 770.457.3221
Fax: 770.454.7419
E-Mail: [hidden email]
Web: http://www.asktami.com/
LinkedIn: http://www.linkedin.com/in/asktami
Twitter: http://twitter.com/asktami
iChat/AIM/Skype: tamiwilliamsusa

FileMaker Solutions Alliance Associate | Lasso Professional Alliance  
Member

------

If you want to receive sporadic email from Creative Computing  
regarding news at the company and announcements about upcoming Lasso  
webinars and online classes, please opt-in at http://tinyurl.com/yj7eqlg


#############################################################
This message is sent to you because you are subscribed to
  the mailing list <[hidden email]>.
To unsubscribe, E-mail to: <[hidden email]>
To switch to the DIGEST mode, E-mail to <[hidden email]>
To switch to the INDEX mode, E-mail to <[hidden email]>
Send administrative queries to  <[hidden email]>