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 '
      
    
       
         
            Number: //customernumber
        Name: //customername
        Postcode://customerpostcode
        Orders open://amount of open orders
        Since://customer since
         
       
    
  
  
    
       
         ';

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 "";

        }
echo ' ".$id."
        $name
        $postcode
        amount
        $csince  
       
    
  
';

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.

spencer7593 · Accepted Answer

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

Show amount of open orders per customer

Answers (2)

Related Questions