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.