Counting instances of id's over multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Counting instances of id's over multiple columns

Author

Topic

sq2
Starting Member

11 Posts

Posted - 2010-06-19 : 17:57:49

Am trying to count how many rows all player_id's exist in, where they can be in one of two columns (challenger or challengee)

this works to get a list of both columns, so very close:

SELECT challenger_id AS player_id, COUNT( * )
FROM `games`
GROUP BY `challenger_id`
UNION ALL
SELECT challengee_id AS player_id, COUNT( * )
FROM `games`
GROUP BY `challengee_id`

taking that one step further, i would have thought this would solve it, but no:

SELECT *, COUNT( * )
FROM (
SELECT challenger_id AS player_id, COUNT( * )
FROM `games`
GROUP BY `challenger_id`
UNION ALL
SELECT challengee_id AS player_id, COUNT( * )
FROM `games`
GROUP BY `challengee_id`
) AS temp
GROUP BY player_id

any ideas?

sq2

Kristen
Test

22859 Posts

Posted - 2010-06-19 : 18:20:45

Those back-ticks suggest you are using mySQL? This is a Microsoft SQL conference so folk here may not be familiar with MySQL.

I would expect you to need to specify the Column name in your second example (rather than "SELECT *"), and you need to SUM the Counts from the inner-select - COUNT(*) [in the outer select] is only going to tell you the number or rows in the inner select:


SELECT player_id, SUM(T_Count)
FROM ( 
    SELECT challenger_id AS player_id, COUNT( * ) AS T_Count
    FROM `games` 
    GROUP BY `challenger_id`
    UNION ALL
    SELECT challengee_id AS player_id, COUNT( * ) AS T_Count
    FROM `games` 
    GROUP BY `challengee_id` 
) AS temp
GROUP BY player_id

sq2
Starting Member

11 Posts

Posted - 2010-06-19 : 18:44:06

perfect thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources