Simple SUM code

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
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Simple SUM code

Post by koolsamule »

Hi Chaps,

Probably a really easy one for you:

I have some PHP/SQL code, that looks at three tables and SUMs up data from a column.
The problem is the data isn't SUMing up correctly, it places the three results after another, instead of SUMing up:

Code: Select all

<?php
// Total Up Words (Net)
$query = "
(
SELECT 
tbl_jobs.jobwnet, SUM(jobwnet) 
FROM 
tbl_projects 
INNER JOIN 
tbl_jobs 
ON tbl_projects.projid=tbl_jobs.FK_projid 
INNER JOIN 
tbl_user_main 
ON tbl_user_main.userid=tbl_jobs.FK_usertranslationid 
WHERE 
tbl_jobs.jobtransih='y' 
AND tbl_jobs.jobtranscomplete='n'
)
UNION
(
SELECT 
tbl_jobtransline.jobwnet, SUM(jobwnet) 
FROM 
tbl_projects 
INNER JOIN 
tbl_jobtransline 
ON tbl_projects.projid=tbl_jobtransline.FK_projid 
INNER JOIN 
tbl_user_main 
ON tbl_user_main.userid=tbl_jobtransline.FK_usertranslationid 
WHERE 
tbl_jobtransline.jobtransih='y' 
AND tbl_jobtransline.jobtranscomplete='n'
)
UNION
(
SELECT 
tbl_jobxml.jobwnet, SUM(jobwnet) 
FROM 
tbl_projects 
INNER JOIN 
tbl_jobxml 
ON tbl_projects.projid=tbl_jobxml.FK_projid 
INNER JOIN 
tbl_user_main 
ON tbl_user_main.userid=tbl_jobxml.FK_usertranslationid 
WHERE 
tbl_jobxml.jobtransih='y' 
AND tbl_jobxml.jobtranscomplete='n'
)
";
 
$result = mysql_query($query) or die(mysql_error());
 
// Print out result
while($row = mysql_fetch_array($result)){
    echo $row['SUM(jobwnet)'];
}
?>
E.g:
tbl_jobs.jobwnet, SUM(jobwnet) = 100
tbl_jobtransline.jobwnet, SUM(jobwnet) = 200
tbl_jobxml.jobwnet, SUM(jobwnet) = 300
Required result = 600
Code result = 100200300
User avatar
Robert07
Forum Contributor
Posts: 113
Joined: Tue Jun 17, 2008 1:41 pm

Re: Simple SUM code

Post by Robert07 »

Code: Select all

 
// Print out result
$total=0;
while($row = mysql_fetch_array($result)){
    $total+=$row['SUM(jobwnet)'];
}
echo $total;
 
Post Reply