Access sql combine SELECT and COUNT function

Question

ALL. I have table looks like

NAME1 NAME2  Result
Jone  Jim     win
Kate  Lucy    loss
Jone  Lucy    win
Jim   Jone    loss

I want to select from NAME1 where win case>=3, My code is

SELECT NAME1,Count(Result='win') as WIN_CASE
From TABLE
Group by NAME1
Having Count(Result='win')>=3;

However, the result is not correct from the output, it just returns the total number of names shown in NAME1, what should I do to fix it please?

UPDATE: Thanks for all the reply. The result from Kritner and jbarker work fine. I just forget to add "where"Clause.

If one of the responses answered your question, you may consider marking this question as answered by selecting one of their responses. — Myles Baker, Oct 13 '14 at 13:11

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Query:

SELECT NAME1, COUNT(Result) AS WIN_CASE
FROM A
WHERE Result='win'
GROUP BY NAME1
HAVING COUNT(Result)>=3

edited Jun 20 '20 at 09:12

Community

answered Oct 13 '14 at 13:00

Mr. Mascaro

score 1 · Answer 2 · answered Oct 13 '14 at 13:06

1

Try this

select * 
from (select NAME1, Result, count(*) as res from test group by Result, NAME1) as t 
where t.res>=3 and t.Result ='win'

answered Oct 13 '14 at 13:06

Zakaria Ouahbi

1

Please explain how this code helps the OP and further readers of this thred. You can find more about answering guidelines in the [help]: [answer] – Pred Oct 13 '14 at 13:33

score 0 · Answer 3 · answered Oct 13 '14 at 12:59

0

You could do this:

select name1
from test
where result = 'win'
group by name1
having count(1) >= 3

Note that this isn't taking into account "Jone" wins when name 2 is jone and there's a loss (at least that's how i'm interpretting your shema.

So if name1 is jone, and it's a win, then jone won. But if jone is name2 and it's a loss, then jone won correct?

answered Oct 13 '14 at 12:59

Kritner

Thanks for the reply. I only want to select the names from NAME1 if and only if it is win and in NAME1. I don't consider jone in NAME2 and result is loss as it is win for jone. – henduo qian Oct 13 '14 at 13:02
May I ask why "count(1)"? why it is "1" not others? – henduo qian Oct 13 '14 at 13:03
No real reason, just quicker to write for me and produces same result. http://stackoverflow.com/questions/1221559/count-vs-count1 – Kritner Oct 13 '14 at 13:05