Querying for certain results

[graham23s]

Hi Guys,

 

With some trial and error i have come up with this query:

 

SELECT COUNT(DISTINCT(`CART_ID`)),`CUSTOMER_ID` FROM `fcp_orders_master_log` GROUP BY `CUSTOMER_ID` ORDER BY COUNT(DISTINCT(`CART_ID`)) DESC

 

which does indeed (appear) to display the ID's of the customers who have placed the most orders DESC, but i'm not sure how to display the number of orders they have placed beside them

 

code:

 

<?php
/***
  * Site statistics
  ***/
  $qRT = "SELECT SUM(`WP_TO_CHARGE`) AS `REVENUE_TAKEN` FROM `fcp_orders_completed` WHERE `WP_TEST_LIVE`='COMPLETED'";
  $rRT = mysql_query($qRT);
  $aRT = mysql_fetch_array($rRT);
  $REVENUE_TAKEN = number_format($aRT['REVENUE_TAKEN'], 2);
  
  $qRP = "SELECT SUM(`WP_TO_CHARGE`) AS `REVENUE_TAKEN` FROM `fcp_orders_completed` WHERE `WP_TEST_LIVE`='LIVE'";
  $rRP = mysql_query($qRP);
  $aRP = mysql_fetch_array($rRP);
  $REVENUE_PENDING = number_format($aRP['REVENUE_TAKEN'], 2);
  
  /***
   * Top customers
   ***/
   $qTO = "SELECT COUNT(DISTINCT(`CART_ID`)),`CUSTOMER_ID` FROM `fcp_orders_master_log` GROUP BY `CUSTOMER_ID` ORDER BY COUNT(DISTINCT(`CART_ID`)) DESC";
   $rTO = mysql_query($qTO);

   $qTC = "SELECT CUSTOMER_ID, SUM(QUANTITY) AS `COUNT` FROM `fcp_orders_master_log` WHERE `PROCESSED`='Y' GROUP BY CUSTOMER_ID ORDER BY SUM(QUANTITY) DESC";
   $rTC = mysql_query($qTC);
?>
<div id="fcp-admin-content" align="center" />

<table align="center" class="fcp-heading-main" border="0" bordercolor="#0000000" width="500" cellpadding="2" cdellspacing="0" />
<tr>
  <td colspan="2" class="fcp-heading" align="left"><b>Site Statistics</b></td>
   </tr> 
<tr>
  <td colspan="2" class="fcp-heading" align="center"><b>Revenue</b></td>
   </tr>
    <tr>
    <td class="fcp-heading-body" align="left">Revenue Taken:</td><td class="fcp-heading-body" align="right"><?php print "£$REVENUE_TAKEN"; ?></td>
   </tr>
    <tr>
    <td class="fcp-heading-body" align="left">Revenue Pending:</td><td class="fcp-heading-body" align="right"><?php print "£$REVENUE_PENDING"; ?></td>
   </tr>
  <tr>
  <td colspan="2" class="fcp-heading" align="center"><b>Top Customers</b></td>
   </tr>
  <tr>
  <td class="fcp-heading" align="center"><b>Customer</b></td><td class="fcp-heading" align="center"><b>Number of Products Purchased</b></td>
   </tr>
   <?php
   while ($aTC = mysql_fetch_array($rTC))
   {   
    $cusCO = $aTC['COUNT'];
    $cusID = $aTC['CUSTOMER_ID'];
    
    /*** 
     * Customers name
     ***/
     $qC = "SELECT `first_name`,`last_name` FROM `fcp_customers` WHERE `id`='$cusID'";
     $rC = mysql_query($qC);
     $aC = mysql_fetch_array($rC);
     
     $FN = $aC['first_name'];
     $LN = $aC['last_name'];
    
    print '<tr>
            <td class="fcp-heading-body" align="left">'.$FN.' '.$LN.'</td><td class="fcp-heading-body" align="center">'.$cusCO.'</td>
           </tr>';
   }
   ?>
  <tr>
  <td colspan="2" class="fcp-heading" align="center"><b>Top Customers (in terms of orders)</b></td>
   </tr>
  <tr>
  <td class="fcp-heading" align="center"><b>Customer</b></td><td class="fcp-heading" align="center"><b>Number of Orders Placed</b></td>
   </tr>
   <?php
   while ($aTO = mysql_fetch_array($rTO))
   {   
    $cusOP = $aTO['CUSTOMER_ID'];
    //$t     = $aTO['COUNT'];
    
    print '<tr>
            <td class="fcp-heading-body" align="left">'.$cusOP.'</td><td class="fcp-heading-body" align="center">'.$t.'</td>
           </tr>';
   }
   ?>
  </table> 
</div>
<?php
include("inc/inc-footer-admin.php");
include("inc/inc-online.php");
?>

 

so ultimately it displays:

 

ID  Number of orders

35          10

122        8

 

kinda thing

 

thanks for any help guys

 

Graham

[gevans]

Change your query to this;

 

SELECT COUNT(DISTINCT(`CART_ID`)) AS count_result,`CUSTOMER_ID` FROM `fcp_orders_master_log` GROUP BY `CUSTOMER_ID` ORDER BY COUNT(DISTINCT(`CART_ID`)) DESC

 

Then you can use the count_result alias as follows;

 

   <?php
   while ($aTO = mysql_fetch_array($rTO))
   {   
    $cusOP = $aTO['CUSTOMER_ID'];
    $t     = $aTO['count_result'];
    
    print '<tr>
            <td class="fcp-heading-body" align="left">'.$cusOP.'</td><td class="fcp-heading-body" align="center">'.$t.'</td>
           </tr>';
   }
   ?>

[graham23s]

that brill thanks mate

 

Graham