max(count ) from 2 tables mysql

July 31, 2022

Finding the city in which the most orders were sent leads to the assignment of the city and the number of orders (the named amount column). I have 2 tables the named Customers and Orders

SELECT Customers.City,count( Orders.OrderID) as amount  
FROM voodoo.Customers
inner join voodoo.Orders on Customers.CustomerID=Orders.CustomerID
group by  Orders.OrderID
having amount =(
select max(x.mycount) 
from (select Orders.OrderID,count(Orders.OrderID) as mycount
from  voodoo.Customers
where Orders.CustomerID in (Customers.CustomerID=Orders.CustomerID ) 
group by Orders.OrderID)as x);

tables

>Solution :

You don’t need a subquery as you can just order by amount (descending) and limit the result to 1:

SELECT Customers.City, count(Orders.OrderID) as amount  
  FROM voodoo.Customers INNER JOIN voodoo.Orders 
    ON Customers.CustomerID=Orders.CustomerID
GROUP BY Orders.OrderID
ORDER BY amount DESC
LIMIT 1;

EDIT: as Thorsten Kettner pointed out, I made a copy & paste error; the correct version would GROUP BY Customers.City.