[SOLVED] Sum + Count

[Matrixkid]

Hi there,

Im trying to take values from two tables and add them. this task is simple enough, but the problem is is that I want to get the count totals from each table first and then add them.

 

Here is the table structure:

---TABLEA-----------------------
--------------------------------
  Player |   Throws  |  Time  |
--------------------------------
   Bob   |      3    |  10-20 |
   Matt  |      1    |  10-20 |
   Sam   |      9    |  11-20 |
   Bob   |      11   |  09-20 |
   Matt  |      2    |  09-20 |
   Bob   |      6    |  17-20 |
--------------------------------

 

I DO NOT want the total throws. I just want the count value of how many times they show up in the table - not the sum of throws.

SELECT Count(throws) AS CountPlayer, player FROM tablea GROUP BY player

 

For TABLEA then, I am given

Bob 3

Matt 2

Sam 1

 

---TABLEB-----------------------
--------------------------------
  Player |   Throws  |  Time  |
--------------------------------
   Matt  |      4    |  10-20 |
   Sam   |      1    |  11-20 |
   Bob   |      2    |  09-20 |
   Matt  |      2    |  09-20 |  
   Bob   |      9    |  03-20 |
   Sam   |      3    |  08-20 |
   Bob   |      1    |  01-20 |
--------------------------------

 

using the count method above, I am given the count values for TABLEB

Bob 3

Matt 2

Sam 2

 

 

So now I want to take the count results from both tables and add them, giving me this:

Bob 6

Matt 4

Sam 3

 

The first query is the one that gets me the closest to numbers, but it only shoots out weird results.

 

SELECT Count(throws) AS CountPlayer, player FROM tableb GROUP BY player

UNION

SELECT Count(throws) AS CountPlayer, player FROM tablea GROUP BY player ORDER BY `CountPlayer` DESC

 

 

SELECT (SELECT Count(throws) AS CountPlayer, player FROM tablea GROUP BY player) + (SELECT Count(throws) AS CountPlayer, player FROM tableb GROUP BY player)

AS total

 

 

SELECT Count(tablea.throws) AS aThrows,Count(tableb.throws) AS bThrows, (aThrows + bThrows) as abTotal

from tablea,tableb

left join tablea on tablea.player = tableb.player

order by abTotal

 

 

 

thanks for the help.

MK

 

 

[Matrixkid]

I have figured out the query:

SELECT Count(throws) AS CountPlayer, player FROM (SELECT throws,player FROM tablea UNION ALL SELECT throws,player from tableb) DERIVEDTBL GROUP BY player ORDER BY `CountPlayer` DESC

 

This works on my localhost mysql query browser, but it wont work on the live server, it says

SHOW TABLE STATUS LIKE 'DERIVEDTBL';

 

turns out mysql doesnt support derivedtbl

 

Hmmmm.