How to Count Orders by State

Question

I have a database that has three tables, RMA, Orders, and Customer and I am trying to count the returns(RMAs) by the State(which is in the customer's table). The query keeps returning only one record though (UTAH counted only).

Answer 1

Your query is missing a group by clause that aggregates records by state. In all other databases other than MySQL, you would get a syntax error... But unfortunately MySQL is lax about that (when option ONLY_FULL_GROUP_BY is disabled), which makes this kind of error harder to spot.

select count(*) as total_returns, c.state
from customers c
inner join orders o on c.customerID = o.customerID
inner join rma r    on o.orderID = r.orderID
group by c.state   --> here

Note that I used table aliases to shorten the query, and changed the count to a simple count(*) (which is equivalent in the query, and more efficient for the database).

If you want to display states that have no returns, then you can use left joins instead:

select count(r.orderID) as total_returns, c.state
from customers c
left join orders o on c.customerID = o.customerID
left join rma r    on o.orderID = r.orderID
group by c.state   --> here