CODEWITHSUNDEEP
phpmysqlsqldatabase
sandip
sandip

Reputation: 3289

How to take multiple column output as one column and find top of that column

I got stuck in a scenario where I am selecting average of each column in a table. Now I want to find the top three of those columns.

here is my query:

SELECT AVG( `adventure` ) , AVG( `beach` ) , AVG( `culture` ) , AVG( `festival` ) , AVG( `food` ) , AVG( `fun` ) , AVG( `biking` ) , AVG( `ski` ) , AVG( `surf` ) , AVG( `family` ) , AVG( `couple` ) , AVG( `single` ) , AVG( `backpacker` )
FROM `ratings`
WHERE `reference_id`=4595

This query returns me average of each column. But I want to select top 3 averages only.

Thanks in advance!

Upvotes: 1

Views: 165

Answers (2)

John Woo
John Woo

Reputation: 263813

I guess you need to normalized your table first but here's a query that suits your needs,

By the way it returns two columns, the names of the column on where the average was calculated and the result of the average.

SELECT  Category, avgVal
FROM
    (
        SELECT  'adventure' AS Category, AVG(`adventure`) AS avgVal FROM `ratings` WHERE `reference_id`=4595
        UNION
        SELECT  'beach' AS Category, AVG(`beach`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595
        UNION
        SELECT  'culture' AS Category, AVG(`culture`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'festival' AS Category, AVG(`festival`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'food' AS Category, AVG(`food`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595
        UNION
        SELECT  'fun' AS Category, AVG(`fun`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'biking' AS Category, AVG(`biking`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'ski' AS Category, AVG(`ski`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'surf' AS Category, AVG(`surf`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'family' AS Category, AVG(`family`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'couple' AS Category, AVG(`couple`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'single' AS Category, AVG(`single`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
        UNION
        SELECT  'backpacker' AS Category, AVG(`backpacker`)  AS avgVal FROM `ratings` WHERE `reference_id`=4595 
    ) s
ORDER BY avgVal DESC
LIMIT 3

Upvotes: 3

Dhruv Patel
Dhruv Patel

Reputation: 404

You will need to use sub query here 

 SELECT AVG( `adventure` ) 
       ,AVG( `beach` )
       ,AVG( `culture` )
       , AVG( `festival` )
       , AVG( `food` )
       , AVG( `fun` )
       , AVG( `biking` )
       , AVG( `ski` )
       , AVG( `surf` )
       , AVG( `family` )
       , AVG( `couple` )
       , AVG( `single` )
       , AVG( `backpacker` ) 
FROM ( SELECT adventure,beach,culture,festival,food,fun,biking,ski,surf,family,couple,single,backpacker
       FROM `ratings`
       WHERE `reference_id`=4595 
       LIMIT 0,3
) DerivedTable

Hope this helps. Thanks

Upvotes: 0

Related Questions