specific count

[madsm]

SELECT distinct hometeamFK,
count(hometeamFK) AS hkampe,
sum(homescore) AS hjemmemaalfor,
sum(awayscore) AS hjemmemaalimod,
leagueFK,
seasonFK,
count(cardtype='red') as redcards,
count(cardtype='yellow) as yellowcards

FROM soccergames AS games
inner join soccercards as cards
on games.soccergameID=cards.soccergameFK

WHERE status='played'

group by hometeamFK, leagueFK, seasonFK

this wuery works fine, but doesn't return only redcards and only yellowcards

it returns the total in both of them... is it even possible to do this ?

thanks

/mads - www.startout.dk

[Anon]

SELECT distinct hometeamFK,
count(hometeamFK) AS hkampe,
sum(homescore) AS hjemmemaalfor,
sum(awayscore) AS hjemmemaalimod,
leagueFK,
seasonFK,
sum(case when cardtype='red' then 1 else 0 end) as redcards,
sum(case when cardtype='yellow' then 1 else 0 end) as yellowcards

FROM soccergames AS games
inner join soccercards as cards
on games.soccergameID=cards.soccergameFK

WHERE status='played'

group by hometeamFK, leagueFK, seasonFK

[madsm]

thanks mate.... just what I was looking for!

/ mads - www.startout.dk