|
Subject:
|
count records where condition not true (IIF??)
|
|
Posted By:
|
rpeksens
|
Post Date:
|
9/19/2003 2:39:48 PM
|
hi all
how do i use 'count' to return instances where the condition does not occur? (count is zero)???
for example, here's the table (TAB1):
Player Home Away
Jim 10 27
Jim 12 13
Ron 10 7
Ron 52 10
Ron 3 17
Dave 17 10
Dave 0 7
for each distinct Player, i want to know how many records have Home>Away, *even if that number is zero*
so in this example, i want to get back:
Player Wins
Jim 0
Ron 2
Dave 1
i tried this:
select distinct Player, IIF(count(*) is null, 0, count(*))
from TAB1
where Home > Away
group by Player
order by Player
but it still doesnt count Jim's zero wins.
thx!
rudi
|
|
Reply By:
|
BethMoffitt
|
Reply Date:
|
9/19/2003 3:30:51 PM
|
Is this for a report?
|
|
Reply By:
|
BethMoffitt
|
Reply Date:
|
9/19/2003 3:34:41 PM
|
Doesn't matter. This will show wins & losses. You can delete the losses column if you wish.
SELECT TAB1.Name, Sum(IIf([Home]>[away],1,"0")) AS Wins, Sum(IIf([Home]<[away],1,"0")) AS Losses
FROM TAB1
GROUP BY TAB1.Name
ORDER BY Sum(IIf([Home]>[away],1,"0"))
WITH OWNERACCESS OPTION;
Regards,
Beth M
|
|
Reply By:
|
BethMoffitt
|
Reply Date:
|
9/19/2003 3:36:57 PM
|
Sorry, change TAB1.Name to TAB1.Player in all instances. It should read:
SELECT TAB1.Player, Sum(IIf([Home]>[away],1,"0")) AS Wins, Sum(IIf([Home]<[away],1,"0")) AS Losses
FROM TAB1
GROUP BY TAB1.Player
ORDER BY Sum(IIf([Home]>[away],1,"0"))
WITH OWNERACCESS OPTION;
Beth M
|
