SUM column from 3 tables?
Posted: Fri Oct 09, 2009 5:40 am
I have this query:
But the SUM doesn't sum up correctly, instead of SUMing up the total of 3 results, it places 3 results one after another:
e.g.:
should be: 100 + 100 + 100 = 300
result: 100100100
How do I SUM up the total of the jobwnet column of the 3 tables?
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.:
should be: 100 + 100 + 100 = 300
result: 100100100
How do I SUM up the total of the jobwnet column of the 3 tables?