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
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.
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
Try this:
select DISTINCT state, (select count(id) as cnt, Orders from customers group by
state ORDER BY Orders DESC) Temp FROM Customers
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