SQL query suggestions/help

[Glenn Carr]

I'm trying to count the number of records in 'game_dates' where the
columns home_team_id or away_team_id have the same value. E.g., i
want to know the number of records for each team_id where team_id is
home_team_id or away_team_id.

I'm doing this in two separate select statements now. Example:

SELECT count(home_team_id),home_team_id FROM games
WHERE league_id = 218 and ((home_score IS NOT NULL OR away_score IS
NOT NULL) OR (home_score <> 0 OR away_score <> 0))
GROUP BY home_team_id
and
SELECT count(away_team_id),away_team_id FROM games
WHERE league_id = 218 and ((home_score IS NOT NULL OR away_score IS
NOT NULL) OR (home_score <> 0 OR away_score <> 0))
GROUP BY away_team_id
and then combining the results. Is there anyway to combine these to
queries into one query? ...and have a single result set returned with
two columns (count,team_id)?

Thanks,
Glenn

[Shervin Shapourian]

Hi Glenn,

Try this:

select team_id = isnull(home_team_id, away_team_id),
count = isnull(s1,0) + isnull(s2,0)
from (SELECT home_team_id, count(home_team_id) as s1
FROM games
WHERE league_id = 218
and ((home_score IS NOT NULL OR away_score IS NOT NULL)
OR (home_score <> 0 OR away_score <> 0))
GROUP BY home_team_id) as T1
full outer join
(SELECT away_team_id, count(away_team_id) as s2
FROM games
WHERE league_id = 218
and ((home_score IS NOT NULL OR away_score IS NOT NULL)
OR (home_score <> 0 OR away_score <> 0))
GROUP BY away_team_id) as T2
on home_team_id = away_team_id

Shervin

gl***@glenncarr.com (Glenn Carr) wrote in message news:<21**************************@posting.google. com>...

I'm trying to count the number of records in 'game_dates' where the
columns home_team_id or away_team_id have the same value. E.g., i
want to know the number of records for each team_id where team_id is
home_team_id or away_team_id.

I'm doing this in two separate select statements now. Example:

SELECT count(home_team_id),home_team_id FROM games
WHERE league_id = 218 and ((home_score IS NOT NULL OR away_score IS
NOT NULL) OR (home_score <> 0 OR away_score <> 0))
GROUP BY home_team_id
and
SELECT count(away_team_id),away_team_id FROM games
WHERE league_id = 218 and ((home_score IS NOT NULL OR away_score IS
NOT NULL) OR (home_score <> 0 OR away_score <> 0))
GROUP BY away_team_id
and then combining the results. Is there anyway to combine these to
queries into one query? ...and have a single result set returned with
two columns (count,team_id)?

Thanks,
Glenn

[Shervin Shapourian]

Oops!

Sorry I forgot the parentheses. Change the last line to this:

on (home_team_id = away_team_id)

Shervin
"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message
news:4e**************************@posting.google.c om...

Hi Glenn,

Try this:

select team_id = isnull(home_team_id, away_team_id),
count = isnull(s1,0) + isnull(s2,0)
from (SELECT home_team_id, count(home_team_id) as s1
FROM games
WHERE league_id = 218
and ((home_score IS NOT NULL OR away_score IS NOT NULL)
OR (home_score <> 0 OR away_score <> 0))
GROUP BY home_team_id) as T1
full outer join
(SELECT away_team_id, count(away_team_id) as s2
FROM games
WHERE league_id = 218
and ((home_score IS NOT NULL OR away_score IS NOT NULL)
OR (home_score <> 0 OR away_score <> 0))
GROUP BY away_team_id) as T2
on home_team_id = away_team_id

Shervin

gl***@glenncarr.com (Glenn Carr) wrote in message

news:<21**************************@posting.google. com>...

I'm trying to count the number of records in 'game_dates' where the
columns home_team_id or away_team_id have the same value. E.g., i
want to know the number of records for each team_id where team_id is
home_team_id or away_team_id.

I'm doing this in two separate select statements now. Example:

SELECT count(home_team_id),home_team_id FROM games
WHERE league_id = 218 and ((home_score IS NOT NULL OR away_score IS
NOT NULL) OR (home_score <> 0 OR away_score <> 0))
GROUP BY home_team_id
and
SELECT count(away_team_id),away_team_id FROM games
WHERE league_id = 218 and ((home_score IS NOT NULL OR away_score IS
NOT NULL) OR (home_score <> 0 OR away_score <> 0))
GROUP BY away_team_id
and then combining the results. Is there anyway to combine these to
queries into one query? ...and have a single result set returned with
two columns (count,team_id)?

Thanks,
Glenn

[Glenn]

Works great! Thanks.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!