Adding numbers from the same column

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ibanez270dx
Forum Commoner
Posts: 74
Joined: Thu Jul 27, 2006 12:06 pm
Location: Everywhere, California

Adding numbers from the same column

Post 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]
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
ibanez270dx
Forum Commoner
Posts: 74
Joined: Thu Jul 27, 2006 12:06 pm
Location: Everywhere, California

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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'];
} 
?>
ibanez270dx
Forum Commoner
Posts: 74
Joined: Thu Jul 27, 2006 12:06 pm
Location: Everywhere, California

Post by ibanez270dx »

Thanks guys! You've really helped me out! This is super-appreciated!
Post Reply