Simple SUM code
Posted: Fri Oct 09, 2009 5:14 am
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:
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
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)'];
}
?>tbl_jobs.jobwnet, SUM(jobwnet) = 100
tbl_jobtransline.jobwnet, SUM(jobwnet) = 200
tbl_jobxml.jobwnet, SUM(jobwnet) = 300
Required result = 600
Code result = 100200300