Page 1 of 1

Adding numbers from the same column

Posted: Thu Jul 27, 2006 12:09 pm
by ibanez270dx
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi,
I am having problems with a little section of my script (running on MySQL 4). Pretty much, I need to extract all everything in my table (no problem), but then I need to take all the numbers (from column "dwntime_hrs") in each row and add them together. How would I go about doing this? 

My script looks like this thus far:

Code: Select all

$sql = "SELECT *, SUM(dwntime_hrs) as total_downtime FROM $table_name WHERE aircraft_id = '$_POST[viewlog]' ORDER BY dwntime_date";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) 
{

$aircraft_id = stripslashes($row['aircraft_id']);
$aircraft_name = stripslashes($row['aircraft_name']);
$dwntime_type = stripslashes($row['dwntime_type']);
$dwntime_date = stripslashes($row['dwntime_date']);
$dwntime_hrs = stripslashes($row['dwntime_hrs']);
$dwntime_reason = stripslashes($row['dwntime_reason']);
$dwntime_log = stripslashes($row['dwntime_log']);
$dwntime_log_by = stripslashes($row['dwntime_log_by']);
$total_dwntime_hrs = $row['total_downtime'];

However, I get this error:

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


Any ideas?

Thanks,
- Jeff


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Thu Jul 27, 2006 1:07 pm
by pickle
The problem is, you're selecting * - which will give you all the rows, but you're also asking for a grouping result - the SUM(). SUM() gives you one answer for all rows, so you need to add a GROUP BY clause. The GROUP BY clause here, would apply the SUM() function to all rows that have common value in whatever column you grouped by.

For example, if your table is structured like this:

Code: Select all

Person   Department   dwntime_hours
112      01           3
113      01           2
114      02           1700
Then a query:

Code: Select all

SELECT
  Department,
  SUM(dwntime_hours)
FROM
  myTable
GROUP BY
  Department
...would show you that department '01' had 5 hours of downtime.


To the best of my knowledge, if you need both all information from the database as well as a sum of all downtime hours, then you have two choices:
  1. Do two queries - one to retrieve everything & one to get the sum
  2. Do one query that retrieves all info, then use PHP to iterate through the results & generate the sum
It's probably better to do two queries.

Posted: Thu Jul 27, 2006 3:45 pm
by ibanez270dx
Hi,
Thanks for your help, but how would I go about actually getting the final summed up variable? This is what I have, which doesn't work...

Code: Select all

$sql = "SELECT aircraft_id, SUM(dwntime_hrs) FROM $table_name GROUP BY aircraft_id";
$result = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) 
{

$total_dwntime_hrs = $row['dwntime_hrs'];

}

Please help!

Thank you,
- Jeff

Posted: Thu Jul 27, 2006 3:51 pm
by RobertGonzalez

Code: Select all

<?php
$sql = "SELECT aircraft_id, SUM(dwntime_hrs) AS hours_total FROM $table_name GROUP BY aircraft_id";
$result = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_array($result))
{
    $total_dwntime_hrs = $row['hours_total'];
} 
?>

Posted: Thu Jul 27, 2006 4:15 pm
by ibanez270dx
Thanks guys! You've really helped me out! This is super-appreciated!