[SOLVED] Simple SUM code

[kool_samule]

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:

 

<?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

[kickstart]

Hi

 

A crude way of doing it:-

 

$query = "SELECT SUM(aSum)
FROM (
SELECT tbl_jobs.jobwnet, SUM(jobwnet)  AS aSum
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)  AS aSum
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) AS aSum
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'
)";

 

All the best

 

Keith

[kool_samule]

Hi Keith, thanks but I got this error:

"Every derived table must have its own alias"

[kickstart]

Hi

 

Ooops, yes. That is selecting from a subselect (ie, the 3 selects unioned together). You need give that subselect an alias (even if you never use that alias name).

 

$query = "SELECT SUM(aSum)

FROM ((

SELECT tbl_jobs.jobwnet, SUM(jobwnet)  AS aSum

..........

AND tbl_jobxml.jobtranscomplete='n'

)) SomeAliasName";

 

All the best

 

Keith

[kool_samule]

Awesome! Cheers Keith, you've done it again. Manyt thanks, Samuel