[SOLVED] wrong sum?

[techker]

hey guys so i got this going but there sum is wrong??

 

$query2 = "SELECT COUNT( `to_t1` )-( `from_t1`)AS TotalNumber, SUM( `to_t1` )-( `from_t1`) AS Totalhours FROM `week1`; "; 
$result2 = mysql_query($query2) or die(mysql_error());

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

  echo $row['Totalhours'] ." H";
}

21H

 

to_t1 is 17:30 and from_t1 is 9:30

 

how does it get 21?i need 8h

 

[techker]

i tried this

 

$query2 = "SELECT COUNT( `to_t1` )-( `from_t1`)  AS Totalhours FROM `week1`; ";

 

but it gives me -7h

[fenway]

I'm confused by the math... what are you trying to count? And for SUM, why isn't the difference inside the parens?

[techker]

see i want to calculat how many hours i have done in a day.and a week.

 

they bust my balls over over time..lol

 

i dont understand the sum part?

[toplay]

Maybe you want something like this:

 

$query2 = "SELECT COUNT(*) AS TotalCount, `to_t1` - `from_t1` AS TotalNumber, SUM( `to_t1` - `from_t1`) AS Totalhours FROM `week1`";

[techker]

that should be it but it gives me this?

 

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

[toplay]

So, add a group by at the end then.

 

group by some_column_name  # Use something relevant to query

[techker]

that did it.did it ask for a group since we added to fileds in one?

 

now i just need to add all the filds up.thx

[techker]

hey is there a way that if the total hours pass 40 it shows in red?

 

i added up all the sums and it works a1!

 

now in the query can i add something like an

 

if Totalhours is over '40' show in red

 

 

[toplay]

Retrieve the data, then use PHP to do it:

 

$color = ($Totalhours > 40) ? 'red' : 'blue';  // $Totalhours var is value retrieved from table result

 

echo 'Total hours: <font color="', $color, '">', $Totalhours, '</font>';  // Better to use CSS - this is just an example

 

 

 

 

 

[techker]

so

 

if $color = ($Totalhours > 40) ? 'red' : 'blue';

 

echo 'Total hours: <font color="', $color, '">', $Totalhours, '</font>';

[toplay]

Wrong, no 'if' at the beginning. Use what I had.

 

This:

 

$color = ($Totalhours > 40) ? 'red' : 'blue';

 

is the same as this:

 

if ($Totalhours > 40)

    $color = 'red';

else

    $color = 'blue';

 

 

See ternary operator:

http://us3.php.net/manual/en/language.operators.comparison.php#language.operators.comparison.ternary