kool_samule Posted October 9, 2009 Share Posted October 9, 2009 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 Quote Link to comment Share on other sites More sharing options...
kickstart Posted October 9, 2009 Share Posted October 9, 2009 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 Quote Link to comment Share on other sites More sharing options...
kool_samule Posted October 9, 2009 Author Share Posted October 9, 2009 Hi Keith, thanks but I got this error: "Every derived table must have its own alias" Quote Link to comment Share on other sites More sharing options...
kickstart Posted October 9, 2009 Share Posted October 9, 2009 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 Quote Link to comment Share on other sites More sharing options...
kool_samule Posted October 9, 2009 Author Share Posted October 9, 2009 Awesome! Cheers Keith, you've done it again. Manyt thanks, Samuel Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.