CODEWITHSUNDEEP
sqlsql-server
Craig P
Craig P

Reputation: 507

SQL query to show result equals 0 where clause is not met

I have the below SQL query. Player 'Judd' does not have an entry where PlaceFinished = 4. 

select PlaceFinished, count(PlaceFinished) as PlaceCount , sum (PointsAwarded) as Points 
from Teams_tbl T
where Player = 'Judd' and PlaceFinished is not NULL and PlaceFinished in (1,2,3,4) 
group by PlaceFinished

The current result is 

PlaceFinished   PlaceCount
1               6
2               2
3               6

I would like it to show:

PlaceFinished   PlaceCount
1               6
2               2
3               6
4               0

I tried to create a virtual table with a Left Outer Join but the result is the same

Declare @Places Table(
place int
)

Insert into @Places
select distinct PlaceFinished from Teams_tbl

select p.place, count(PlaceFinished) as PlaceCount , sum (PointsAwarded) as Points from Teams_tbl T
Left Outer Join @Places P
on T.PlaceFinished = p.place
where Player = 'Judd' and PlaceFinished is not NULL group by p.place

Upvotes: 0

Views: 101

Answers (2)

Xingzhou Liu
Xingzhou Liu

Reputation: 1559

Right join because you are looking for a place (fourth) that doesn't have a row in placefinished as Judd has never placed fourth. PlaceFinished is not null filters out all places that the player has never come in, so get rid of it. Also, filter for Judd using a derived table, so that join stays outer. Everything else is good :-)

select p.place, 
       count(PlaceFinished) as PlaceCount , 
       sum (PointsAwarded) as Points
from 
(Select placefinished as ..., FROM Teams_tbl Where Player='Judd') T
 Right Outer Join 
 @Places P
 on T.PlaceFinished = p.place 
 group by p.place

Upvotes: 0

jarlh
jarlh

Reputation: 44766

If you know that all PlaceFinished values exist in the table, regardless of player, you can do conditional aggregation:

select PlaceFinished,
       count(case when Player = 'Judd' then 1 end) as PlaceCount,
       sum(case when Player = 'Judd' then PointsAwarded else 0 end) as Points 
from Teams_tbl T
where PlaceFinished in (1,2,3,4) 
group by PlaceFinished

Upvotes: 2

Related Questions