CODEWITHSUNDEEP
sqlms-access
henduo qian
henduo qian

Reputation: 93

Access SQL with count function for multiple fields

All. I have table looks like

Team1 Team2   result
  a    b        win
  a    c        tie
  b    d        loss
  c    a        win
  d    c        win

I want to output team1 result statistics like

Team1  win  loss  tie
  a     4    2    1
  b    2     2    3

I can count win cases using 

SELECT TEAM1,Count(Result='win') as Win_Number
From GAMES
Where Result='win'
Group by TEAM1

but what should I add to output loss and tie? using join?

UPDATE: using "iif sum" command solve the problem, see XQbert's post. Many thanks.

Upvotes: 0

Views: 3197

Answers (1)

xQbert
xQbert

Reputation: 35353

This is one way...

I keep forgetting case doesn't work in inline select in MSAccess

So this will not work:

SELECT [Home team],
  sum(CASE WHEN result='win' THEN 1 else 0 END) as Win,
  sum(CASE WHEN result='loss' THEN 1 else 0 END) as Loss,
  sum(CASE WHEN result='tie' THEN 1 else 0 END) as Tie
FROM GAMES
GROUP BY [Home team]

But this may:

SELECT  [Home team]
    sum(iif(result='win', 1,0)) as Win,
    sum(iif(result='loss', 1,0)) as Loss,
    sum(iif(result='tie', 1,0)) as Tie,
FROM GAMES
    GROUP BY [Home team]

This basically says evaluate each result in table grouped by home team and set the value to 1 if result is a win, loss or tie, otherwise set the value to 0. It will then SUM all of the results for a given home team. since each result is either a win loss or tie, a 1 will appear in either win, loss or tie but only one of the three; and possibly none if result is other than win loss or tie.. Though I stand behind my use a cross tab query statement below.

So data would look like this... before the sum

Team1  win  loss  tie
  a     1    0    0
  a     1    0    0
  a     1    0    0
  a     1    0    0
  a     0    1    0
  a     0    1    0
  a     0    0    1
  b     1    0    0
  b     1    0    0
  b     0    1    0
  b     0    1    0
  b     0    0    1
  b     0    0    1
  b     0    0    1

and then the sum would occur giving you...

Team1  win  loss  tie
  a     4    2    1
  b     2    2    3

Access has a nice feature called cross tab query. change the query type to this and play around with it. It's a nice feature that access has that many large databases don't! IT allows you to create columns based on a the value of a row (Result in your case) and then aggregate by it (count the times win/loss tie appear for a given team)

Example: https://support.office.com/en-us/article/Make-summary-data-easier-to-read-by-using-a-crosstab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8

How to: http://office.microsoft.com/en-us/access-help/create-a-select-or-crosstab-query-mdb-HP005187965.aspx

Upvotes: 1

Related Questions