GROUP BY NULL - 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

GROUP BY NULL

Author

Topic

grecian
Starting Member

18 Posts

Posted - 2006-04-15 : 16:46:23

I've read that

Any field containing a NULL value will be ignored in the statistical calculations performed by the aggregate functions.

[url]http://www.devguru.com/Technologies/jetsql/quickref/group_by.html[/url]

is there any way that i can get these null values to NOT be ignored and counted/summed with all the rest?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-15 : 17:21:31

u want null to be considered as zero ?

Srinika

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 17:27:01

Wherever the column appears in an aggregate put
coalesce(mycol,0)
e.g.
sum(coalesce(mycol,0))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

grecian
Starting Member

18 Posts

Posted - 2006-04-15 : 18:48:06

quote:
Originally posted by Srinika

u want null to be considered as zero ?

yes

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-15 : 19:00:39

U can use the ISNULL(NullableField,0) instead of NullableField

If u need some more detailed answer,please post some sample data and expected results

Srinika

grecian
Starting Member

18 Posts

Posted - 2006-04-15 : 19:00:44

quote:
Originally posted by nr

Wherever the column appears in an aggregate put
coalesce(mycol,0)
e.g.
sum(coalesce(mycol,0))

My code looks thus

SELECT COUNT (COALESCE (PlayerFixture.FixtureID,0)) FROM PlayerFixture WHERE PlayerFixture.Comptype = 'FAC' AND PlayerFixture.PlayerID = '::PlayerID::' GROUP BY PlayerFixture.Season

but i get the error message
Undefined function 'COALESCE' in expression.

I want it to return a zero each time there is a season where the count for PlayerFixture.FixtureID WHERE PlayerFixture.Comptype = 'FAC' AND PlayerFixture.PlayerID = '::PlayerID::' equals zero.

Any ideas?
Thank you

grecian
Starting Member

18 Posts

Posted - 2006-04-15 : 19:05:18

quote:
Originally posted by Srinika

U can use the ISNULL(NullableField,0) instead of NullableField

If u need some more detailed answer,please post some sample data and expected results

Srinika

This gives me the error message

Wrong number of arguments used with function in query expression 'COUNT (ISNULL (PlayerFixture.FixtureID,0))'.

There may not be any records that exist for PlayerFixture.FixtureID WHERE PlayerFixture.Comptype = 'FAC' AND PlayerFixture.PlayerID = '::PlayerID::' in which case i want to return a zero

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 19:24:14

how about count(*)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

grecian
Starting Member

18 Posts

Posted - 2006-04-15 : 19:40:28

quote:
Originally posted by nr

how about count(*)

tried

SELECT COUNT (*) FROM PlayerFixture WHERE PlayerFixture.Comptype = 'FAC' AND PlayerFixture.PlayerID = '::PlayerID::' GROUP BY PlayerFixture.Season

but if still doesn't return a zero if no fields exist

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-15 : 19:51:02

I'm a bit confused about what you want.
That will return the number of entries in the group (whether the value in any column is null or not) - sounded like that was what you wanted.

If there are no entries (I think that's what you mean by no fields) then you won't have a group so can't get anything.

Maybe if you post an example of what you are looking for.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-04-15 : 23:08:15

Are you using SQL Server?

grecian
Starting Member

18 Posts

Posted - 2006-04-16 : 02:38:02

I have a table

PLAYERFIXTURE
FixtureID
PlayerID
Number
Goals
Comptype
Season

and it contains data relating to which players played in which game what number they wore and how many goals they scored in what season and in which competition.

I want to count games played in a particular season, which is all very well if they played games because it wil count and group by season specifically, but if they didn't play any games in a particular season i don't get anything returned when i deally i would like a 0 (zero) or '-' (dash) returned. I'm confused. I'm using ms access database and custom queries within FrontPage 2003

Cheers

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-16 : 09:17:47

So you want an entry for every player in the table for every season.
You need to get a list of all the players and seasons - which you can't do by just using the PLAYERFIXTURE table. I'm a bit surprised you don't have other tables with all the players and seasons but you can derive it from PLAYERFIXTURE.

select pf.PlayerID, pf.Season ,
games = sum(case when p.PlayerID is null then 0 else 1 end)
from (select distinct PlayerID from PLAYERFIXTURE) p
cross join
(select distinct Season from PLAYERFIXTURE) s
left join PLAYERFIXTURE pf
on pf.PlayerID = p.PlayerID
and pf.Season = s.Season
group by pf.PlayerID, pf.Season

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

grecian
Starting Member

18 Posts

Posted - 2006-04-16 : 09:40:11

I do have other tables called

FIXTURES
FixtureID
FixDate
Season
OpponentID
OppScore
CityScore
VenueID
Atten
CompID
Comptype
Result

and

PLAYERS
PlayerID
Surname
FirstName
Played

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-16 : 10:00:54

Then
select pf.PlayerID, pf.Season ,
games = sum(case when p.PlayerID is null then 0 else 1 end)
from PLAYERS p
cross join
(select distinct Season from PLAYERFIXTURE) s
left join PLAYERFIXTURE pf
on pf.PlayerID = p.PlayerID
and pf.Season = s.Season
group by pf.PlayerID, pf.Season

If you had a Season table you could get rid of the other derived table - and also get a result for the season before the the fixtures were input - would also be able to prevent invalid seasons in the fixture table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-04-16 : 10:27:44

Nigel -- he's using Access.

You should post your questions in the Access forum, this site is for SQL Server.

grecian
Starting Member

18 Posts

Posted - 2006-04-16 : 10:38:33

I'm sorry but i don't understand, Should i have a SEASON table and if so what fields should i have? In my FIXTURES table the Season field contains vales such as 1989/90, these can be returned as they are, as this is what i want to display.

An example of my PlayerFixture table is

PlayerID FixtureID Number Goals Comptype Season
SmithA 80901 1 0 L 1908/09
JonesB 80901 2 0 L 1908/09
...
SmithA 80902 1 0 FAC 1908/09
JonesB 80902 2 1 FAC 1908/09
...
SmithA 91001 1 0 L 1909/10
JonesB 91001 2 1 L 1909/10
...
BrownC 91002 1 0 FAC 1909/10
JonesB 91002 2 1 FAC 1909/10

when i query a particular players history i want to dsiplay a table that looks thus
for A Smith

-----------------------------------------------------------
| | League | FAC | TOTAL |
|Season | Apps | Goals | Apps | Goals | Apps | Goals |
-----------------------------------------------------------
|1908/09 | 1 | 0 | 1 | 0 | 2 | 0 |
|1909/10 | 1 | 0 | 0 | 0 | 1 | 0 |
-----------------------------------------------------------

The problem is occuring where i can count the games that SmithA has played in but because he didn't play any FAC games in the 1909/10 season i don't get anything returned instead i when i group by Season i only get returned the number of games he played in in seasons where he did play and not when he didn't.

The number in bold above is the number i'm trying to generate by saying if i don't have any data for him for that competition (because he didn't play) return a zero or even a dash.

Sorry to be a pain,

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-16 : 10:48:01

>> Nigel -- he's using Access.
in that case

select pf.PlayerID, pf.Season ,
sum(iif(t.PlayerID is null, 0, 1)) as games
from (select distinct p.PlayerID, s.Season from PLAYERFIXTURE as p ,PLAYERFIXTURE as s) as t
left outer join PLAYERFIXTURE as pf
on pf.PlayerID = t.PlayerID
and pf.Season = t.Season
group by pf.PlayerID, pf.Season

or as you have a PLAYERS table

select pf.PlayerID, pf.Season ,
sum(iif(t.PlayerID is null, 0, 1)) as games
from (select distinct p.PlayerID, s.Season from PLAYERS as p ,PLAYERFIXTURE as s) as t
left outer join PLAYERFIXTURE as pf
on pf.PlayerID = t.PlayerID
and pf.Season = t.Season
group by pf.PlayerID, pf.Season

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

grecian
Starting Member

18 Posts

Posted - 2006-04-16 : 10:53:55

Thank you, sorry for being a stupid but what does t.PlayerID mean? what table does t represent and also will this show results for just FAC games or all games?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-16 : 11:06:22

t is an alias for the derived table which gives all the combinations of seasons and players
(select distinct p.PlayerID, s.Season from PLAYERS as p ,PLAYERFIXTURE as s) as t

What's an FAC game?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

grecian
Starting Member

18 Posts

Posted - 2006-04-16 : 11:13:35

In my desired results the player plays League games and FAC games (FA Cup) i want to distinguish between the two and show how many games the player has played in the FA Cup even if he has played none in that particular season.
Thank you

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources