SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SUM column from 3 tables?

    I have this query:
    Code:
    <?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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i thought you said you wanted a php solution?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2009
    Location
    Latvia, Riga
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SUM() summs column values! Nothing more. It's working as it should

    You can try solution like this:
    Code SQL:
    SELECT (SELECT t1.FIELD FROM `table1` t1 WHERE id=1) + (SELECT t2.FIELD FROM `table2` t2 WHERE id=1) + (SELECT t3.FIELD FROM `table3` t3)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    anpher, if you're going to show syntax for adding up the three sums, you could at least show the syntax for the UNION query posed in this thread, not a generic scenario that you made up
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2009
    Location
    Latvia, Riga
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why should I do that? I didn't mean to do his job. I was tyring to show the way he should think.
    I throw idea and your job is to use it or search for better solution. That's all.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why?

    because in this case the "idea" you "threw" won't work

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2009
    Location
    Latvia, Riga
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And where is the difference between my sample and this case?
    Each SELECT returns one value (I believe that of "tbl_jobs.jobwnet, SUM(jobwnet)" he needs only "tbl_jobs.jobwnet"). So where is the problem?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the difference is, your query returns exactly one row, and his returns multiple rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    May 2009
    Location
    Latvia, Riga
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kool_samule View Post
    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
    I assume that he needs only one row wich value is sum of all rows.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Anpher View Post
    I assume that ...
    hang on a second, i'll get right back to you

    hey kool_samule, how come the three subselects in your UNION don't have a GROUP BY clause?

    because in the SELECT clauses, you mix both non-aggregate and aggregate columns, which is invalid without a GROUP BY clause

    i assumed you'd get multiple rows per jobwnet
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •