CODEWITHSUNDEEP
phpmysqlsqlcount
Jack Hillard
Jack Hillard

Reputation: 606

Need help figuring out MySQL query to count if a certain number

I have a table setup like this:

fruit1 | fruit2 | fruit3 | fruit4 | number1 | number2 | number3 | number4
--------------------------------------------------------------------------
apple  | orange | banana |  berry |    5    |    2    |    1    |    4   
orange | banana | apple  |  berry |    3    |    2    |    5    |    2
berry  | banana | orange |  apple |    1    |    2    |    5    |    2

I need a MySQL query to count how many times a given fruit is number 5. In this example, apple is 5 twice and orange is 5 once. Does that make sense? fruit1's number is number1, fruit2's number is number2, etc...

I could do this with a bit of php code but I know it's a pretty simple MySQL query. I just have the hardest time putting queries together.

Thanks in advance!

Upvotes: 0

Views: 203

Answers (1)

John Woo
John Woo

Reputation: 263803

SELECT fruit, COUNT(*) NumberOfInstance
FROM
(
    SELECT fruit1 fruit, number1 num FROM table1
    UNION ALL
    SELECT fruit2 fruit, number2 num FROM table1
    UNION ALL
    SELECT fruit3 fruit, number3 num FROM table1
    UNION ALL
    SELECT fruit4 fruit, number4 num FROM table1
) s
WHERE num = 5
GROUP BY fruit

Upvotes: 1

Related Questions