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).

I've attached a screencap of the code.

Please don't post code and error messages as an image but rather as code-formatted text since none of us can copy, paste and run an image. For more on this, please see [**Discourage screenshots of code and/or errors**](https://meta.stackoverflow.com/a/285557/2275490) — Vickel, Jun 20 '20 at 18:08
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Jun 20 '20 at 18:22

score 0 · Accepted Answer · answered Jun 20 '20 at 18:10

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

How to Count Orders by State

1 Answers1