Show amount of open orders per customer

Question

I'm stuck for a while on a piece of code of which I can't find the solution.. Tried a whole bunch of options but none of it seems to work.

I have a table with all my customers. Its showing their name, postcode etc. But I also want to show the amount of open orders in the same table.

I got these tables mysql tables:

TABLE 1

Tablename: customer

Columns: customer_ID, postcode, customer_since, customer_name

TABLE 2

Tablename: status

Columsn: status_ID, status_name

TABLE 3

Tablename: orders

Columns: order_ID, customer_ID, status_ID

So far this is my code:

$sql = mysql_query ("SELECT customer.customer_ID, customer.postcode, customer.since, customer.name
FROM customer
ORDER BY customer.customer_ID desc  ");

echo '<table border="0" width="515"  >
      <tr>
    <td>
       <table cellspacing="0" cellpadding="0" border="0" width="515" id="table1" >
         <tr>
            <th width="60" align="center"><span class="tabledescription">Number:</span></td> //customernumber
        <th width="155" align="center"><span class="tabledescription">Name:</span></td> //customername
        <th width="100" align="center"><span class="tabledescription">Postcode:</span></td>//customerpostcode
        <th width="100" align="center"><span class="tabledescription">Orders open:</span></td>//amount of open orders
        <th width="100" align="center"><span class="tabledescription">Since:</span></td>//customer since
         </tr>
       </table>
    </td>
  </tr>
  <tr>
    <td>
       <div style="width:565px; height:322px; overflow:auto;">
         <table id="table1" cellspacing="0" cellpadding="0" border="0" width="575" >';

while($row = mysql_fetch_array($sql, MYSQL_ASSOC))
{
$id = $row['customer_ID'];
$name= $row['name'];
$postcode = $row['postcode'];
$status = $row['status'];
$since = $row['customer_since'];
$probleem = $row['probleem'];

$csince = date('d-m-Y', $since);

echo "<tr><td width=64><a style=' color: #009bce; text-decoration: none;' href='detailvieuwcustomer.php?id=".$id."'>".$id."</a></td>
        <td width=160>$name</td>
        <td width=105>$postcode</td>
        <td width=105>amount</td>
        <td width=105>$csince</td></tr>";

        }
echo ' </table>  
       </div>
    </td>
  </tr>
</table>'; 

So far that is working and showing my 8 customers at this moment. I have 7 different status types for each order. The last one is that its delivered so that one isn't open. I made this code:

$statusnumber = 7;


$sql1 = mysql_query("SELECT * FROM order WHERE customer_ID = '". $id . " ' AND status_ID != '". $statusnumber . "' ");

while($prow = mysql_fetch_array($sql1, MYSQL_ASSOC))
{

$openstatus = $prow['storing_ID'];

echo $openstatus;

This one is showing me every order which doesn't has status_ID 7.

Now I don't know how to count the number of orders which got status_ID 1 - 6 and put the amount of open orders in the table behind the right customer.

I also tried to join the tables:

$sql = mysql_query("SELECT status.status_ID, order.status_ID, order.customer_ID, customer.customer_ID, customer.name, customer.postcode, customer.since
        FROM order
        INNER JOIN status on (status.status_ID = order.status_ID)
        INNER JOIN customer on (customer.customer_ID = order.customer_customer_ID)
        ORDER BY customer.customer_ID desc "); 

But when I do that its showing me all my customers multiple times, because he's getting the customer_ID from orders, and I got around 30 orders. Its giving me a result like: 1,1,1,1,2,2,2,3,4,4,5,5,5,5 etc.

I just can't seem to display all customers 1 time with the right amount of orders they have open..

Help would be appreciated.

Answer 1

There's a couple of approaches.

One is to do an OUTER JOIN to the order table. The trick here is the GROUP BY on the customer_ID, and doing a check on the status_ID column to return either a 0 or a 1, and then adding up the 0s and 1s with a SUM group aggregate function:

 SELECT c.customer_ID
      , c.postcode
      , c.since
      , c.name
      , SUM(IF(s.status_ID != 7,1,0)) AS open_order_count
  FROM customer c
  LEFT
  JOIN order o
    ON o.customer_ID = c.customer_ID
  LEFT
  JOIN status s
    ON s.status_ID = o.status_ID
 GROUP
    BY c.customer_ID
     , c.postcode
     , c.since
     , c.name
 ORDER
    BY c.customer_ID DESC

NOTE: we could use a COUNT aggregate in place of the SUM, but we would need to return a NULL for those rows we didn't want to count...

      , COUNT(IF(s.status_ID != 7,1,NULL)) AS open_order_count

---

Another approach (usually less performant on large sets), is to use a correlated subquery in the SELECT list:

 SELECT c.customer_ID
      , c.postcode
      , c.since
      , c.name
      , ( SELECT SUM(IF(s.status_ID != 7,1,0))
            FROM order o
            LEFT
            JOIN status s
              ON s.status_ID = o.status_ID
           WHERE o.customer_ID = c.customer_ID
        ) AS open_order_count
  FROM customer c
 ORDER BY c.customer_ID DESC

---

NOTE: For performance, I would probably avoid the join to the status table, and shortcut the check on status_ID by looking at just the order table. (It really depends on why the status table is being included in the query; I just don't see a need for it here.) e.g.

 SELECT c.customer_ID
      , c.postcode
      , c.since
      , c.name
      , ( SELECT SUM(IF(o.status_ID != 7,1,0))
            FROM order o
           WHERE o.customer_ID = c.customer_ID
        ) AS open_order_count
  FROM customer c
 ORDER BY c.customer_ID DESC

---

Another approach is to use an inline view to get the open order counts for all customers, and then join that to the customer table...

 SELECT c.customer_ID
      , c.postcode
      , c.since
      , c.name
      , IFNULL(r.open_order_count,0) AS open_order_count
  FROM customer c
  LEFT
  JOIN (
         SELECT o.customer_ID
              , SUM(IF(o.status_ID != 7,1,0)) AS open_order_count
           FROM order o
          GROUP
             BY o.customer_ID
       ) r
    ON r.customer_ID = o.customer_ID
 ORDER BY c.customer_ID DESC

Answer 2

There are two parts to this question:

1. How do I know when orders are open or not? Relying on "status IDs 1-6" is not usually the right approach. What happens when you add another status? Now "open" means "status IDs 1-6 and 8?" You can see this gets out of hand quickly. The better approach is to add an is_open flag to the status table (probably to the order table too, but that's just for historical purposes and may not be necessary in your application).

2. How do I get a count of open orders for each customer? Well the answer to this question depends on your solution to the previous, but this should do the trick (assuming you have added an is_open column to your status table which is a TINYINT(1) and set the flag correctly on each of the order statuses):

   SELECT status.status_ID, order.status_ID, order.customer_ID, customer.customer_ID, customer.name, customer.postcode, customer.since, COUNT(DISTINCT IF(status.is_open = 1, order.order_ID, NULL)) AS open_orders FROM customer LEFT JOIN order ON (order.customer_ID = customer.customer_ID) LEFT JOIN status ON (status.status_ID = order.status_ID) GROUP BY customer.customer_ID

Notice the use of a LEFT JOIN instead of an INNER JOIN so that customers will show up in your customer list with or without an active order.