Tough Mysql Query ....

Question

Hi guys, I am trying to do a complex query from two tables...It's a question from a skill quiz....

table

**Orders                                    Customer**s
    id                                         id
    date                                     first_name
    shipping_amount                          last_name 
    order_status                             city
    customer_id (Customers.id)             state 

output

-----------+------------+
| State     |  # Orders  |
+-----------+------------+
| NY        |  55        |
| CA        |  40        |
| NJ        |  33        |
| FL        |  21        |
| MO        |  12        |
+-----------+------------+

I have been working on my query and it looks like this...

select DISTINCT state, (select count(id) Orders from customers group by state ORDER BY Orders DESC) FROM Customers

It gave me an error that says subquery returns more than 1 row

It might still be far from the answer but I am not sure what to do. Any helps would be appreciated..

Answer 1

Try this:

SELECT c.state, COUNT(o.id) AS Orders 
FROM Customers c, Orders o 
WHERE o.customer_id = c.id 
GROUP BY state 
ORDER BY Orders DESC

The sub query isn't necessary.

Answer 2

SELECT `Customers`.`state`, count(`Orders`.`id`) 
as `orders FROM `Customers` 
LEFT JOIN `Orders` ON `Customers`.`id` = `Orders`.`customer_id` 
GROUP BY `Customers`.`state` 
ORDER BY `orders` DESC

Answer 3

Try this:

select DISTINCT state, (select count(id) as cnt, Orders from customers group by
    state ORDER BY Orders DESC) Temp FROM Customers

Answer 4

SELECT c.state as State, COUNT(o.id) as NumOrders
FROM orders o
LEFT JOIN customers c ON (c.id = o.customer_id)
GROUP BY c.state