count intressting 1

[russland]

Hi,

Table to hold soccer scores:
ID, ScoreTypeID, UserID, SeasonID

Since there are 3 Type of Scores (ScoreTypeID -> Goal (regular), Goal (penalty), Assist) it makes it a bit hard for me find out in ONE SINGLE QUERY how many Goals, Penalties and Assists a Players (UserID) scored within a Season (SeasonID).

I'd love to see results like this:
=======================================
userid, season, goal, penaly, assist
47, 03/04, 7, 1, 11
47, 04/05, 4, 2, 8
47, 05/06, 1, 0, 2

hmmm... this must be doable.

thanks

 

[PHV]

Something like this ?
SELECT UserID, SeasonID
, SUM(CASE WHEN ScoreTypeID='Goal (regular)' THEN 1 ELSE 0 END) Goal
, SUM(CASE WHEN ScoreTypeID='Goal (penalty)' THEN 1 ELSE 0 END) Penalty
, SUM(CASE WHEN ScoreTypeID='Assist' THEN 1 ELSE 0 END) Assist
FROM yourTable
GROUP BY UserID, SeasonID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[russland]

Okay, thats fancy and neat! worthwhile a star!

 

[russland]

hi again,
further down the road I was thinking of a more dynamic way of querying by ScoreTypeID, since it's easily possible to that there are more ScoreTypes added to the System (e.g. 2nd Assist as in Streethockey.) Case-statement doesn't help in this case.

do you have any clue making it more dynamic? I wasn't able to TSQL commands/operators to do so. That I'd appreciate, as well.

Thanks heaps