CODEWITHSUNDEEP
sqldatabasems-access
user3186940
user3186940

Reputation: 9

Producing a report that measures a value, grouped by one attribute and then by another

I have a database that holds a number of information about sports, teams, the fixtures they play in, the leagues they are in, and the various results of those fixtures. I want to be able to produce a report that measures the team performance (Number of wins? Count value of wins/draws/losses?) and then I want it grouped by the sport each team plays and then grouped by the league and division.

For example,

Team Name         Sport        League         Division    Result
Hampshire Utd     Football     Silver Cup     1           --

I have information for a selection of fixtures that the team have played in, whether they won "W", lost "L", or drew "D" the game. I'm not sure how to set it so that it would count the number of wins. If I try the Count function it counts up every fixture with a value, so the wins, losses and draws: 

Team Name         Sport        League         Division    Result
Grahamshire       Rugby        Phoenix        3           0
Hampshire Utd     Football     Silver Cup     1           5
Kia Ora           Squash       National Open  4           2

If I set the criteria to "W" it brings up the error:

Data type mismatch in criteria expression

I'm not sure how to specifically group by sport, league, and division. Would it be a sort or is there another option in Access that allows me to group these values? I'll provide any other information if necessary, sorry if this is unclear. Thanks.

Upvotes: 0

Views: 31

Answers (1)

StewartDouglas
StewartDouglas

Reputation: 41

To solve this in SQL Server (and assuming all the data sits in a single table, which I've called your_table_name) I'd suggest using the following:

SELECT team_name,
   sport, 
   league,
   division,
   SUM(CASE WHEN Result = 'W' THEN 1 ELSE 0 END) AS wins,
   SUM(CASE WHEN Result = 'L' THEN 1 ELSE 0 END) AS loses,
   SUM(CASE WHEN Result = 'D' THEN 1 ELSE 0 END) AS draws
FROM your_table_name
GROUP BY team_name,
     sport,
     league,
     division
ORDER BY wins DESC -- default is ASC

Upvotes: 0

Related Questions