SitePoint Sponsor

User Tag List

Results 1 to 17 of 17

Thread: SUM, 2 tables

  1. #1
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SUM, 2 tables

    Hi,

    I'm trying to get the sum of 2 fields from different tables. Only the below code show correct results:
    HTML Code:
    $query = mysql_query("
      SELECT (SELECT SUM(var) FROM t1 WHERE t1.name = t2.name + (SELECT SUM(var) FROM t2 WHERE t1.name = t2.name) AS cvar, t1.name AS name
      FROM t1, t2
      WHERE t1.name = t2.name
      GROUP BY t1.name
      ORDER BY SUM(t1.var) + SUM(t2.var) DESC");
      while ($career = mysql_fetch_array($query))
      {
    	echo $career['name'] . ' - ' .$career['cvar'] .  '<br />';
      }
    It works but it's not sorted out correctly. Also, I would have wanted to show the results even if t1.name is not present in t2 and vice versa, but if I remove the WHERE clause after the SUM functions, it throws up an error, same when I change the ORDER BY clause to anything.

    Help with be really, and I mean REALLY appreciated.

    Thanks in advance,
    darkwind777

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i bet it actually ~doesn't~ work -- you're missing a parenthesis

    try this, there is no join assumed, so you can have a t1 without a matching t2 and vice versa --
    Code:
    SELECT u.name
         , SUM(u.subtot1) AS total1
         , SUM(u.subtot2) AS total2
      FROM ( SELECT name 
                  , SUM(var) AS subtot1 
                  , NULL     AS subtot2
               FROM t1 
             GROUP
                 BY name 
             UNION ALL
             SELECT name 
                  , NULL     AS subtot1
                  , SUM(var) AS subtot2 
               FROM t2 
             GROUP
                 BY name 
           ) AS u
    GROUP 
        BY u.name
    ORDER 
        BY total1 + total2 DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you.

    It throws an error though.

    Below is the query using your code:
    Code:
    $query = mysql_query("
      SELECT u.name, SUM(u.subtot1) AS total1, SUM(u.subtot2) AS total2
      FROM ( SELECT CONCAT(fname,' ', lname), SUM(pts) AS subtot1 , NULL AS subtot2 FROM career GROUP BY CONCAT(fname,' ', lname) 
      UNION ALL
      SELECT CONCAT(first_name,' ', last_name), NULL AS subtot1, SUM(pts) AS subtot2 FROM boxscores GROUP BY CONCAT(first_name,' ', last_name)) AS u
      GROUP BY u.name
      ORDER BY total1 + total2 DESC");
      while ($career = mysql_fetch_array($query))
      {
    	echo $career['u.name'] . ' - ' .$career['total1'] . ' - ' .$career['total2'] .  '<br />';
      }
    Here is the actual query that was working, but not the way I would have liked:
    Code:
    $query = mysql_query("SELECT (SELECT SUM(pts) FROM career WHERE CONCAT(fname,' ', lname) = CONCAT(first_name,' ', last_name)) + (SELECT SUM(pts) FROM boxscores WHERE CONCAT(fname,' ', lname) = CONCAT(first_name,' ', last_name) AND boxscores.season != '2007-2008') AS cpts, CONCAT(fname,' ', lname) AS name
      FROM career, boxscores
      WHERE CONCAT(career.fname,' ', career.lname) = CONCAT(boxscores.first_name,' ', boxscores.last_name)
      GROUP BY CONCAT(boxscores.first_name,' ', boxscores.last_name)
      ORDER BY SUM(career.pts) + SUM(boxscores.pts) DESC");
      while ($career = mysql_fetch_array($query))
      {
    	echo $career['name'] . ' - ' .$career['cpts'] .  '<br />';
      }

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by darkwind777 View Post
    It throws an error though.
    perhaps you would be kind enough to tell me what the error was

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

  5. #5
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Below is the error:
    Code:
    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's just the generic php error telling you something was wrong

    please run the query outside of php for the real error

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

  7. #7
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    running it inside phpmyadmin gives the below error:
    HTML Code:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '")' at line 6
    EDIT: below was the error:
    Code:
    #1054 - Unknown column 'u.name' in 'field list'

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you please copy/paste exactly what you ran in phpmyadmin

    i think you might have included the closing doublequote from the original php string assignment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah i did,

    I ran again this query
    Code:
    SELECT u.name, SUM(u.subtot1) AS total1, SUM(u.subtot2) AS total2
      FROM ( SELECT CONCAT(fname,' ', lname), SUM(pts) AS subtot1 , NULL AS subtot2 FROM career GROUP BY CONCAT(fname,' ', lname) 
      UNION ALL
      SELECT CONCAT(first_name,' ', last_name), NULL AS subtot1, SUM(pts) AS subtot2 FROM boxscores GROUP BY CONCAT(first_name,' ', last_name)) AS u
      GROUP BY u.name
      ORDER BY total1 + total2 DESC
    and got this error:
    Code:
    #1054 - Unknown column 'u.name' in 'field list'

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    now we're getting someplace

    the query i gave you did have a column called name, your query didn't

    try it like this --
    Code:
    SELECT u.name
         , SUM(u.subtot1) AS total1
         , SUM(u.subtot2) AS total2
      FROM ( 
           SELECT CONCAT(fname,' ', lname) AS name
                , SUM(pts) AS subtot1
                , NULL AS subtot2 
             FROM career 
           GROUP 
               BY fname
                , lname 
           UNION ALL
           SELECT CONCAT(first_name,' ', last_name)
                , NULL AS subtot1
                , SUM(pts) AS subtot2 
             FROM boxscores 
           GROUP 
               BY first_name
                , last_name
           ) AS u
    GROUP 
        BY u.name
    ORDER 
        BY total1 + total2 DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

    Ran it on phpmyadmin, giving me this error:
    Code:
    #1247 - Reference 'total1' not supported (reference to group function)

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    weird, never saw that one before

    okay, change the ORDER BY as follows:
    Code:
    ORDER 
        BY SUM(u.subtot1) + SUM(u.subtot2)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

    Currently it displays all names which is what I wanted.
    One thing is, it is still not sorted correctly.

    The ones which has records in the table boxscores seems to be the only one sorted, after that list, the rest of the records are displayed unsorted.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you show some example rows from the result set please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the first 10 rows
    Code:
    Jeffrey Cariaso 	8566 	357
    Johnny Abarrientos 	8552 	61
    Dennis Espino 	7897 	638
    Rodney Santos 	8087 	29
    Marlou Aquino 	7325 	366
    Olsen Racela 	6722 	403
    Danny Ildefonso 	6396 	267
    Danny Seigle 	6246 	305
    Willie Miller 	5293 	1116
    Ali Peek 	5581 	551
    And here are 10 rows from the middle part of the results:
    Code:
    Reda Rhalimi 	225  	NULL
    Ernest Brown 	59 	NULL
    Vidal Massiah 	11 	NULL
    Aldrech Ramos 	NULL 	9
    Chris Tiu 	NULL 	21
    Japeth Aguilar 	NULL 	49
    Kelvin Greorio 	NULL 	0
    Mike Burtscher 	NULL 	11
    Tiras Wade 	NULL 	270
    Rashon Turner 	24 	NULL
    Victor Pablo 	7474 	NULL
    Al Vergara 	NULL 	19

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    well, the first 10 rows look correct

    but we've got to handle the NULLs --
    Code:
    ORDER 
        BY COALESCE(SUM(u.subtot1),0) 
         + COALESCE(SUM(u.subtot2),0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow!

    It works perfectly!

    A HUGE thanks!


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
  •