CODEWITHSUNDEEP
phpmysqlmysql-error-1242
FatDeveloper
FatDeveloper

Reputation: 79

Mysql Query: error that says subquery returns more than 1 row

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

Upvotes: 0

Views: 341

Answers (4)

dockeryZ
dockeryZ

Reputation: 3981

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

Upvotes: 0

Sarfraz
Sarfraz

Reputation: 382831

Try this:

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

Upvotes: 0

Dan Heberden
Dan Heberden

Reputation: 11068

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

Upvotes: 0

Ross
Ross

Reputation: 511

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.

Upvotes: 4

Related Questions