SUM, 2 tables

Hi,

I’m trying to get the sum of 2 fields from different tables. Only the below code show correct results:

$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

i bet it actually ~doesn’t~ work – you’re missing a parenthesis :wink:

try this, there is no join assumed, so you can have a t1 without a matching t2 and vice versa –

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

Thank you.

It throws an error though.

Below is the query using your 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:

$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 />';
  }

perhaps you would be kind enough to tell me what the error was

:slight_smile:

Below is the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

that’s just the generic php error telling you something was wrong

please run the query outside of php for the real error

:slight_smile:

running it inside phpmyadmin gives the below error:

#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:

#1054 - Unknown column 'u.name' in 'field list'

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

yeah i did,

I ran again this 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

and got this error:

#1054 - Unknown column 'u.name' in 'field list'

now we’re getting someplace :slight_smile:

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

try it like this –

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

Thanks!

Ran it on phpmyadmin, giving me this error:

#1247 - Reference 'total1' not supported (reference to group function)

weird, never saw that one before

okay, change the ORDER BY as follows:

ORDER 
    BY SUM(u.subtot1) + SUM(u.subtot2)

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.

could you show some example rows from the result set please

Here is the first 10 rows

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:

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

well, the first 10 rows look correct

but we’ve got to handle the NULLs –

ORDER 
    BY COALESCE(SUM(u.subtot1),0) 
     + COALESCE(SUM(u.subtot2),0)

Wow!

It works perfectly!

A HUGE thanks!