SUM column from 3 tables?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

SUM column from 3 tables?

Post by koolsamule »

I have this query:

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)'];
}
?>
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?
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: SUM column from 3 tables?

Post by papa »

Instead of echo $row['SUM(jobwnet)'];

you can try:

Code: Select all

 
//loop
$sum +=   $row['SUM(jobwnet)'];
//end of loop
 
echo "Total: ". $sum;
 
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Re: SUM column from 3 tables?

Post by koolsamule »

thanks man!
Post Reply