mysql query for multiple fields

Question

I am using mysql for survey answers. the schema of the survey table is as follows:

id | rating1 | rating2 | rating3 | rating4 | .... | rating20 | 1 10 10 2 8 .... 4 2 8 8 8 5 .... 7 As you can see, there's rating scores (from 1 to 10) inserted into rating1 - rating20 fields. I can easily find biggest or smallest scores from rating(1-20) fields for each row using this query: "SELECT greatest(rating1, rating2 .. rating20) FROM TBL_SURVEY WHERE id=1" which returns 10 for id 1. But I don't know that greatest score belongs which field. And I like to count distnct rating scores. How many 10s or any other scores from rating1-rating20 fields for id 1? Is there mysql queries for this? or Is there a way to get what I want using php?

Any help would be very appreciated...

Thanks Alex, then what is the better database schema for this kind of work? I'd welcome any suggestions. — user1942626
– user1942626, Commented Jun 7, 2017 at 16:24
@user1942626 Most database functions are designed to operate on records (rows), not columns. MySQL's GREATEST() function is leading you down the wrong path. Get your data into normalized records and your problem becomes much easier. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 7, 2017 at 16:28

Tim Biegeleisen · Accepted Answer · 2017-06-07 16:22:57Z

1

I'm going to suggest that you change your table design to something like this:

id | rating_id | rating
1  | 1         | 10
1  | 2         | 10
1  | 3         | 2
1  | 4         | 8
etc.

With this improved and normalized design, we can find the max rating and its corresponding rating_id using a query like this:

SELECT t1.*
FROM ratings t1
INNER JOIN
(
    SELECT id, MAX(rating) AS max_rating
    FROM ratings
    GROUP BY id
) t2
    ON t1.id = t2.id AND
       t1.rating = t2.max_rating

For your second question of e.g. how many 10s there are for id 1 we could use this:

SELECT COUNT(*)
FROM ratings
WHERE id = 1 AND rating = 10

answered Jun 7, 2017 at 16:22

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1942626 Over a year ago

Thanks Tim, but with this table structure, a lot of records will be created.

Collectives™ on Stack Overflow

mysql query for multiple fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related