2

I have an example table like this:

Month City Person
8 LHR ABC
10 BEIJING BCS
11 NY JJJ
11 VENICE hghg
11 VENICE KKK
12 NY aa
12 ORL abc
12 ORL bbc

So what I want to achieve is see the city in a specific month with the most number of people visiting

Like the output should be: 

12 ORL  
11 VENINCE  
10 BEIJING  
8 LHR

I have tried grouping it like 

SELECT month, city , count(*) AS 'no of people visiting'     
FROM table
GROUP BY month, city

This table does tell me which city and month location had how many people
visiting but I cannot extract the the top most month and city combination
with respect to a certain month.

Updated Query (with error)

SELECT *  
FROM   
( SELECT monthname(reservation.PickupDate), location.LocationName, COUNT(*) AS count
FROM reservation  NATURAL JOIN location
WHERE reservation.pickupdate >= DATE_ADD(NOW(), INTERVAL - 3 MONTH)
GROUP BY month(reservation.PickupDate), location.LocationName) AS t1
WHERE NOT EXISTS (SELECT 1
FROM reservation R1 NATURAL JOIN location L1
WHERE  monthname(R1.PickupDate) = monthname(t1.PickupDate)
GROUP BY month(R1.PickupDate), L1.LocationName)
Furqan Tariq
  • 73
  • 1
  • 12

1 Answers1

1

Starting from your query, you just need to eliminate those rows having another city with more visitors on that month:

SELECT * 
FROM
  (SELECT `month`, city, count(*) AS cnt 
  FROM `table` 
  GROUP BY `month`, city) t1
WHERE NOT EXISTS(SELECT 1 
  FROM `table` t2 
  WHERE t2.`month` = t1.`month`
  GROUP BY `month`, city 
  HAVING count(*) > t1.cnt)
Amir Rahimi Farahani
  • 1,580
  • 1
  • 12
  • 14
  • Could you explain 'Select 1'? I understand the concept behind this query but this little part I am confused about – Furqan Tariq Apr 26 '15 at 13:33
  • Since the sub-query is not meant to return a result set and we are just checking for existence of some data, it does not matter what you select there. You can replace with *, or any fields or constants you like. See http://stackoverflow.com/questions/6137433/where-does-the-practice-exists-select-1-from-come-from – Amir Rahimi Farahani Apr 26 '15 at 13:39
  • The query gives error when the FROM table is a joined table. e.g (SELECT `month`, city, count(*) AS cnt FROM table1 natural join table2 GROUP BY `month`, city) t1 WHERE NOT EXISTS(SELECT 1 FROM table1 natural join table2 WHERE t1.`month` = table1.`month` GROUP BY `month`, city HAVING count(*) > t1.cnt) In the where not exists clause t1 is not recognized – Furqan Tariq Apr 26 '15 at 14:27
  • What is the error? Please update your question with your final query. – Amir Rahimi Farahani Apr 26 '15 at 14:29
  • Ok the actual query I was writing is as follows: SELECT * FROM ( SELECT monthname(reservation.PickupDate), location.LocationName, COUNT(*) AS count FROM reservation NATURAL JOIN location WHERE reservation.pickupdate >= DATE_ADD(NOW(), INTERVAL - 3 MONTH) GROUP BY month(reservation.PickupDate), location.LocationName) AS t1 WHERE NOT EXISTS (SELECT 1 FROM reservation R1 NATURAL JOIN location L1 WHERE monthname(R1.PickupDate) = monthname(t1.PickupDate) GROUP BY month(R1.PickupDate), L1.LocationName) The error was Unknown column t1.pickupdate – Furqan Tariq Apr 26 '15 at 14:40
  • Ignoring the other details the main problem was that t1.attributename was not being recognized inside the WHERE NOT EXISTS CLAUSE – Furqan Tariq Apr 26 '15 at 14:42
  • The t1 sub-query has 3 columns and non of them is named `pickupdate`. Set an alias for calculated fields then you can use them in the exists clause just like what I did for `count(*)`: `monthname(reservation.PickupDate) as PickupMonth` – Amir Rahimi Farahani Apr 26 '15 at 14:52