CODEWITHSUNDEEP
mysqlsqlcountsum
beerye
beerye

Reputation: 263

Count of multiple columns

I am having trouble writing a query that sums the occurrence of all values in 2 columns. I have a table with the following structure: 

+-----------+------------+
| player1ID | player2ID  |
+-----------+------------+
| 1         | 2          |
| 1         | 3          |
| 2         | 3          |
| 1         | 4          |
+-----------+------------+

After running the query I want a result table like this:

+-----------+------------+
| playerID  | count      |
+-----------+------------+
| 1         | 3          |
| 2         | 2          |
| 3         | 2          |
| 4         | 1          |
+-----------+------------+

I have tried the following query

select g1.player1ID, g1.count1 + g2.count2 
from 
    (select player1ID, count(*) from table group by player1ID) as g1, 
    (select player2ID, count(*) from table group by player2ID) as g2 
where player1ID = player2ID

but that only gives the count if the player occurs in both columns (player1ID & player2ID) but won't come up if it only occurs in one or the other.

Upvotes: 2

Views: 64

Answers (2)

jpw
jpw

Reputation: 44881

You could use union in a derived table like this:

select player, count(*) as count
from (
  select player1id player from table1
  union all
  select player2id player from table1
) sub
group by player;

Sample SQL Fiddle

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Use union all to combine the two columns. Then do the aggregation:

select playerID, count(*)
from ((select player1ID as playerID from table) union all
      (select player2ID as playerID from table)
     ) t
group by playerID;

Upvotes: 0

Related Questions