[SOLVED] Stupid SUM() Problem

[rwachowiak]

<?php include 'libs/dbopen.php';
	$id = (isset($_GET['Userid']))? mysql_escape_string($_GET['Userid']) : "1";
	$query  = "SELECT SUM(week1-week1hours) FROM staff JOIN project ON project.staff_id=staff.id WHERE project.staff_id='$id'";
	$result = mysql_query($query) or die(mysql_error());
	while($row = mysql_fetch_array($result)){  

	if ($row['SUM(week1-week1hours)'] >= 0)	{	echo "{$row['SUM(week1-week1hours)']}";	}
	else			{	echo "<span class='red'>({$row['SUM(week1-week1hours)']})</span>";	}
	}

	include 'libs/dbclose.php';?>

 

Ok, so what i want this to do is SUM up all of week1 and minus week1hours... but what its doing is its multiplying week1hours by how many ever rows there are of week1... so if theres 5 rows it looks like: "week1-week1hours*5"

 

any ideas?!

 

thanks!!

[Jabop]

This is a MySQL question, for the MySQL Help section

[rwachowiak]

whoops sorry did it get moved already?

[adam84]

$query = "SELECT SUM(week1-week1hours) AS theSum FROM staff JOIN project ON project.staff_id=staff.id WHERE project.staff_id='$id'";
$result = mysql_query($query) or die(mysql_error());

$totalRows = mysql_num_rows( $result );
while($row = mysql_fetch_array($result)){ 

if ($row['theSum'] > -1){
	echo $row['theSum'] * $totalRows;
else{
	echo "<span class='red'>".$row['theSum'] . "</span>";
}

[rwachowiak]

$query = "SELECT SUM(week1-week1hours) AS theSum FROM staff JOIN project ON project.staff_id=staff.id WHERE project.staff_id='$id'";
$result = mysql_query($query) or die(mysql_error());

$totalRows = mysql_num_rows( $result );
while($row = mysql_fetch_array($result)){ 

if ($row['theSum'] > -1){
	echo $row['theSum'] * $totalRows;
else{
	echo "<span class='red'>".$row['theSum'] . "</span>";
}

 

hmm, i put that in place of what i had and it breaks the page, makes it all white... hmmm

[Barand]

Can you post table structures, sample data and expected results. It's a lot easier if I can see what you've got.

[rwachowiak]

Can you post table structures, sample data and expected results. It's a lot easier if I can see what you've got.

 

TABLE: project

FIELDS: id, staff_id, clientname, projecttype, partner, manager, week1, week2 ~ week 59, week 60, wk1act, wk2act ~ wk59act, wk60act

 

id staff_id clientname projecttype partner manager week1 week2

1  1  Mader Construction  Audit  KGB  EDC  10  10

2  1  Taber Industries  1120S Review  CWC  CAJ  5  5

 

 

TABLE: staff

FIELDS: id, firstname, lastname, skilllevel, week1hours, week2hours ~ week59hours, week60hours

 

id firstname lastname skilllevel week1hours week2hours

1  Ryan  Wachowiak  Intern  40  40

 

 

 

So, what i want it to do is add up Week1 hours from PROJECT, and do the following formula: "week1hours - SUM of week1"

 

So, for Ryan Wachowiak, it would be -25

 

 

That help?

 

 

[bluejay002]

wow... that's a lot of fields you got their. just my idea though, are you adding additional fields for some time? IMHO, it's not a good idea to have that many fields... maybe a little normalization would do.

 

anyway, that's only a suggestion. going back to your question, try this:

 

SELECT DISTINCT staff.firstname,

staff.lastname,

(staff.week1hours - SUM(project.week1)) as resultOfDeduction

FROM staff

INNER JOIN project ON project.staff_id = staff.id

GROUP BY staff.id

 

did it work? (you can add additional info if you want though)

 

Jay,

[rwachowiak]

wow... that's a lot of fields you got their. just my idea though, are you adding additional fields for some time? IMHO, it's not a good idea to have that many fields... maybe a little normalization would do.

 

anyway, that's only a suggestion. going back to your question, try this:

 

SELECT DISTINCT staff.firstname,

staff.lastname,

(staff.week1hours - SUM(project.week1)) as resultOfDeduction

FROM staff

INNER JOIN project ON project.staff_id = staff.id

GROUP BY staff.id

 

did it work? (you can add additional info if you want though)

 

Jay,

 

what would the whole thing look like? including the echo and stuff? sorry im still pretty new to mysql =(

[Barand]

A GROUP BY gives distinct results so the DISTINCT is superfluous

 

<?php
$sql = "SELECT staff.firstname,
        staff.lastname,
        (staff.week1hours - SUM(project.week1)) as resultOfDeduction
        FROM staff
        INNER JOIN project ON project.staff_id = staff.id
        GROUP BY staff.id";
$res = mysql_query($sql);
while (list($fn, $ln, $tot) = mysql_fetch_row($res))
{
    echo "$fn, $ln, $tot<br/>";
}

?>

[bluejay002]

A GROUP BY gives distinct results so the DISTINCT is superfluous

 

Is that so?! Thanks for the new food for thought

 

Anyway, I think that query should work, if not, just tell us then.

[fenway]

DISTINCT is almost always the wrong modifier.

[rwachowiak]

A GROUP BY gives distinct results so the DISTINCT is superfluous

 

<?php
$sql = "SELECT staff.firstname,
        staff.lastname,
        (staff.week1hours - SUM(project.week1)) as resultOfDeduction
        FROM staff
        INNER JOIN project ON project.staff_id = staff.id
        GROUP BY staff.id";
$res = mysql_query($sql);
while (list($fn, $ln, $tot) = mysql_fetch_row($res))
{
    echo "$fn, $ln, $tot<br/>";
}

?>

 

sorry to be a huge pain, i might be missing something. nothing is showing up on the page for this query =(

[bluejay002]

hmmm... can you put some (just a little) actual data that you have? I think the query did well.

[Barand]

Before I posted I set up tables with the data you provided earlier and got

 

Ryan, Wachowiak, 25