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]

Re: SQL query suggestions/help

Hi Glenn,

Try this:

select team_id = isnull(home_tea m_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



glenn@glenncarr .com (Glenn Carr) wrote in message news:<21951231. 0309221321.7393 029b@posting.go ogle.com>...[color=blue]
> 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[/color]

[Shervin Shapourian]

Re: SQL query suggestions/help

Oops!

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

on (home_team_id = away_team_id)

Shervin


"Shervin Shapourian" <ShShapourian@h otmail.com> wrote in message
news:4eaef17a.0 309221924.2f50e e52@posting.goo gle.com...[color=blue]
> Hi Glenn,
>
> Try this:
>
> select team_id = isnull(home_tea m_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
>
>
>
> glenn@glenncarr .com (Glenn Carr) wrote in message[/color]
news:<21951231. 0309221321.7393 029b@posting.go ogle.com>...[color=blue][color=green]
> > 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[/color][/color]

[Glenn]

Re: SQL query suggestions/help

Works great! Thanks.

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