Left join aggregate question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Left join aggregate question

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-09-02 : 12:43:21

The following query

SELECT
Position.Position_ID,
COUNT(Position_SKill.Position_ID)as PositionSkillCount
FROM Position
LEFT JOIN
Position_Skill
ON Position.Position_ID=Position_Skill.Position_ID
GROUP BY Position.Position_ID

returns 0's for PositionSkillCount where the Position_Skill entry does not exist.

The following query

SELECT
Position.Position_ID,
COUNT(ISNULL(Position_SKill.Position_ID,0))as PositionSkillCount
FROM Position
LEFT JOIN
Position_SKill
ON POsition.Position_ID=Position_SKill.Position_ID
GROUP BY Position.Position_ID

returns 1's for PositionSkillCount where the Position_Skill entry does not exist.

Why is this?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-02 : 12:51:01

I believe the warning: Null value is eliminated in aggregate function

may mean that the record is disregarded in the aggregate function if the value is null

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 12:52:37

well
COUNT(Position_SKill.Position_ID) does not count nulls

as
COUNT(ISNULL(Position_SKill.Position_ID,0))

counts nulls because they are acctually being converted to zero.

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources