SQL COUNT, SUM AND GROUP BY - SQL Server Forums

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

SQL COUNT, SUM AND GROUP BY

Author

Topic

grecian
Starting Member

18 Posts

Posted - 2006-04-05 : 07:45:23

Hello
I'm putting together a football database and want to show how many games a player played in a season.

SELECT COUNT (PlayerFixture.FixtureID) FROM PlayerFixture WHERE PlayerFixture.PlayerID = '::PlayerID::' GROUP BY Season

However if a player doesn't play any games in a particular season the COUNT function ignores it and just returns values in seasons he has played. Is there any way i can get the COUNT function to return a "0" or "-" if a player didn't play any games that season?

Cheers

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-05 : 07:49:21

Please explain scenario with some sample data ..

you can always you case when clause..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-05 : 08:00:41

Build a table of players and set them all to 0, then update them if they have values..

grecian
Starting Member

18 Posts

Posted - 2006-04-05 : 08:05:29

A table such e.g.

FixtureID PlayerID Season
91001 SMITHA 1909
91002 SMITHA 1909
91003 SMITHA 1909
111201 SMITHA 1911
111202 SMITHA 1911

Would give a result using the above SQL of

# of games played
3
2

when really i want a result of

# of games played
3
0
2

Any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 08:24:30

Use LEFT JOIN of a number table to your table.
You can make use of F_TABLE_NUMBER_RANGE from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

select NUMBER as Season, count(t.Season)
from   dbo.F_TABLE_NUMBER_RANGE(1909, 1911) n left join yourtable t
       on n.NUMBER = t.Season
group by NUMBER

grecian
Starting Member

18 Posts

Posted - 2006-04-05 : 09:17:06

Sorry i don't understand do i need another table? And if so what fields do i need?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 09:22:23

You can use another table or use the function table F_TABLE_NUMBER_RANGE as what i did

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 09:23:27

Do you have any other table called games?

Madhivanan

Failing to plan is Planning to fail

grecian
Starting Member

18 Posts

Posted - 2006-04-05 : 11:27:57

Yes its called Fixtures and has fields such as home score, away score, season, date, opponent, venue etc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-06 : 03:31:32

Post table structures, some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-06 : 05:03:09

I guess you need to do count using left outer join with the fixtures table then its should work..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources