max(count ) from 2 tables mysql-CodePudding

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  Customers.City
having amount >= all(select count(Orders.OrderID) 
from  voodoo.Customers
inner join voodoo.Orders on Customers.CustomerID=Orders.CustomerID 
group by Customers.City);

tables

CodePudding user response：

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.

CodePudding user response：

You are looking for the order count per city, not per order. So, don't group by order, but by city. For the ranking of the cities you can use RANK or DENSE_RANK.

SELECT city, amount
FROM
(
  SELECT
    c.city,
    COUNT(o.orderid) AS amount,
    RANK() OVER (ORDER BY COUNT(o.orderid) DESC) AS rnk
  FROM voodoo.customers c
  INNER JOIN voodoo.orders o ON o.customerid = c.customerid
  group by c.city
) counted_and_ranked
WHERE rnk = 1;