Access SQL with count function for multiple fields

Question

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.

xQbert · Accepted Answer · 2014-10-13 14:57:47Z

1

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

edited Oct 13, 2014 at 14:57

answered Oct 13, 2014 at 14:37

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

henduo qian Over a year ago

Thanks for reply. BTW, what will "THEN", "else" and "END" do please?

Alex K. Over a year ago

You need iif rather than case in Access; sum(iif(result='win', 1, 0)) as Win, (missing commas too)

Collectives™ on Stack Overflow

Access SQL with count function for multiple fields

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related