presently there is a table which records information about users enquiring for fares to a particular destination. in the existing report which is a php file when a particular date is selected this php file will display the total number of enquiries for each destination and its price for a particular dates. in this php file a select query is executed along with GROUP BY and ORDER BY ex= DESTINATION PRICE sydney 1000 melbourne 2500 perth 2000 sydney 2000 sydney 3000 my question is 1. i would like to add all sydney fares and find the average and similarly for other cities. ex= DESTINATION PRICE sydney 500 melbourne 200 2. i would like to display the city which had the maximum number of enquiries to the city with minimum enquiries in a descending order with the numeric value. please provide the code for 1 and 2. thanks in advance.
If I understand, you have an sql table that looks like this: mysql> select * from fares; +-------------+-------+ | destination | price | +-------------+-------+ | sydney | 1000 | | melbourne | 2500 | | perth | 2000 | | sydney | 2000 | | sydney | 3000 | +-------------+-------+ Code (markup): If you are looking for PHP code, you'll need to describe what you want the output to look like. But the queries can be: 1) AVERAGE: mysql> SELECT destination,AVG(price) AS average FROM fares GROUP BY destination; +-------------+---------+ | destination | average | +-------------+---------+ | melbourne | 2500 | | perth | 2000 | | sydney | 2000 | +-------------+---------+ Code (markup): 2) HIGHEST TO LOWEST TOTALS: mysql> SELECT destination,count(destination) AS total FROM fares GROUP BY destination ORDER BY total DESC; +-------------+-------+ | destination | total | +-------------+-------+ | sydney | 3 | | melbourne | 1 | | perth | 1 | +-------------+-------+ Code (markup): I hope this helps!