Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    Originally Posted by gw1500se
    That is why I thought this was a MySQL question. The total for every row will be the same until the group changes? Then the OP's code needs to determine when the group changes and use the total for that group. Unless I am still not understanding what sum does when used with group by. In any case I'm not sure what the OP really wants so we need to wait for a response to clarify.
    im not sure if i need to be clearer in what i want to do

    i have a list of properties with a link to view their bills that have been added.

    the link from this page is

    PHP Code:
    <a href="property-utility-list.php?recordID=<?php echo $row_Recordset1['prop_id']; ?>">Utility Bill List</a>
    and the query for this page is

    PHP Code:
    mysql_select_db($database_hostprop$hostprop);
    $query_Recordset1 "SELECT prop_id, prop_type, prop_add1, town, `full` FROM host_editprop WHERE `full` = 'yes'";
    $query_limit_Recordset1 sprintf("%s LIMIT %d, %d"$query_Recordset1$startRow_Recordset1$maxRows_Recordset1);
    $Recordset1 mysql_query($query_limit_Recordset1$hostprop) or die(mysql_error());
    $row_Recordset1 mysql_fetch_assoc($Recordset1); 
    then i have the property-utility-list.php that show all the bill individually as a list

    the query is

    PHP Code:
     $colname_rsProperty "-1"
    if (isset(
    $_GET['recordID'])) { 
      
    $colname_rsProperty $_GET['recordID']; 

    mysql_select_db($database_hostprop$hostprop); 
    $query_rsProperty sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID"GetSQLValueString($colname_rsProperty"text")); 
    $rsProperty mysql_query($query_rsProperty$hostprop) or die(mysql_error()); 
    $row_rsProperty mysql_fetch_assoc($rsProperty); 
    $totalRows_rsProperty mysql_num_rows($rsProperty); 

    mysql_select_db($database_hostprop$hostprop);//this query is showing all totals from the column host_editpropUtil.utilityAmount 
    $query_rsSum sprintf("SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id GROUP BY host_editprop.prop_id"GetSQLValueString($colname_rsProperty"text")); 
    $rsSum mysql_query($query_rsSum$hostprop) or die(mysql_error()); 
    $row_rsSum mysql_fetch_assoc($rsSum); 
    $totalRows_rsSum mysql_num_rows($rsSum); 
    then the table looks like

    PHP Code:
    <table width="940" border="1" align="left" cellpadding="0" cellspacing="0">
                  <tr>
                    <td bgcolor="#B5EC2B" class="table-text">Utility</td>
                    <td bgcolor="#B5EC2B" class="table-text">Date Paid</td>
                    <td bgcolor="#B5EC2B" class="table-text">Start Date</td>
                    <td bgcolor="#B5EC2B" class="table-text">End Date</td>
                    <td bgcolor="#B5EC2B" class="table-text">Amount</td>
                    <td bgcolor="#B5EC2B" class="table-text">Tenants</td>
                    <td bgcolor="#B5EC2B"><span class="table-text">Delete Utility</span></td>
                  </tr>
                  <?php do { ?>
                    <tr>
                      <td class="table-text"><a href="utility-breakdown.php?recordID=<?php echo $row_rsProperty['utilityID']; ?>"><?php echo $row_rsProperty['utilName']; ?></a></td>
                      <td class="table-text"><?php echo date('D, d/m/Y',strtotime($row_rsProperty['utilityDatePaid'])); ?></td>
                      <td class="table-text"><?php echo date('D, d/m/Y',strtotime($row_rsProperty['utilitySD'])); ?></td>
                      <td class="table-text"><?php echo date('D, d/m/Y',strtotime($row_rsProperty['utilityED'])); ?></td>
                      <td class="table-text"><?php echo DoFormatCurrency($row_rsProperty['utilityAmount'], 2',''.'' '); ?></td>
                      <td class="table-text"><?php echo $row_rsProperty['utilityStudAmount']; ?></td>
                      <td class="table-text"><a href="delete-utility.php?utilityID=<?php echo $row_rsProperty['utilityID']; ?>">Delete </a></td>
                    </tr>
                    <?php } while ($row_rsProperty mysql_fetch_assoc($rsProperty)); ?>
                </table>
    and in this table i want the total of the utilityAmount somewhere else on the page.

    PHP Code:
              <?php
             
    // echo $row_rsSum['SUM(host_editpropUtil.utilityAmount)'];
    while ( $row_rsSum mysql_fetch_assoc($rsSum) ) 
        echo 
    $row_rsSum['utilityTotal'].'<br />'
              
    ?>
    I hope this helps thanks
    Last edited by jonnyfreak; April 19th, 2013 at 04:01 PM.
  2. #17
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,343
    Rep Power
    594
    So where does the by group come in when calculating the total? If this is for a single property, why is the total for that property not what you want?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #18
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    Originally Posted by gw1500se
    So where does the by group come in when calculating the total? If this is for a single property, why is the total for that property not what you want?
    I used GROUP because this is what the error told me to do

    yes that is what i want, the total for that property. ALL Utilities for that property
  6. #19
  7. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,343
    Rep Power
    594
    They you don't need the group by and the value of the sum should be correct.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #20
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    Originally Posted by gw1500se
    They you don't need the group by and the value of the sum should be correct.
    if i remove the GROUP BY i get the error

    Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

    PHP Code:
     $colname_rsProperty "-1"
    if (isset(
    $_GET['recordID'])) { 
      
    $colname_rsProperty $_GET['recordID']; 

    mysql_select_db($database_hostprop$hostprop); 
    $query_rsProperty sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID"GetSQLValueString($colname_rsProperty"text")); 
    $rsProperty mysql_query($query_rsProperty$hostprop) or die(mysql_error()); 
    $row_rsProperty mysql_fetch_assoc($rsProperty); 
    $totalRows_rsProperty mysql_num_rows($rsProperty); 
    // 
    mysql_select_db($database_hostprop$hostprop);//this query is showing all totals from the column host_editpropUtil.utilityAmount 
    $query_rsSum sprintf("SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id"GetSQLValueString($colname_rsProperty"text")); 
    $rsSum mysql_query($query_rsSum$hostprop) or die(mysql_error()); 
    $row_rsSum mysql_fetch_assoc($rsSum); 
    $totalRows_rsSum mysql_num_rows($rsSum); 
  10. #21
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by jonnyfreak
    if i remove the GROUP BY i get the error
    if you want to run the query and get the sum for only one property, then you should remove the GROUP BY clause, but also, and this is just as important, remove the property from the SELECT clause, and add a WHERE condition for that specific property

    come to think of it, you don't need the join either
    Code:
    SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal
      FROM host_editpropUtil
     WHERE host_editpropUtil.prop_id = 937 -- or whatever
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #22
  13. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,343
    Rep Power
    594
    Originally Posted by jonnyfreak
    if i remove the GROUP BY i get the error

    Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
    That is why this is a MySQL question. I am not a query expert but I know the group by is what is messing you up. I don't understand why you are getting that error but you need to be able obtain the sum of that column without using group by. There is something else that does not belong and I suspect it is the second table reference as r937 suggested.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo