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
– Myles Baker, Commented Oct 13, 2014 at 13:11

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Query:

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 13, 2014 at 13:00

Mr. Mascaro

2,7431 gold badge13 silver badges18 bronze badges

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

Comments

Zakaria Ouahbi · Accepted Answer · 2014-10-13 13:06:42Z

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, 2014 at 13:06

Zakaria Ouahbi

112 bronze badges

1 Comment

Pred Over a year ago

Please explain how this code helps the OP and further readers of this thred. You can find more about answering guidelines in the help center: How to Answer

Kritner · Accepted Answer · 2014-10-13 12:59:09Z

0

You could do this:

http://sqlfiddle.com/#!6/02c47/1

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, 2014 at 12:59

Kritner

13.8k11 gold badges53 silver badges76 bronze badges

3 Comments

henduo qian Over a year ago

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 Over a year ago

May I ask why "count(1)"? why it is "1" not others?

Kritner Over a year ago

No real reason, just quicker to write for me and produces same result. stackoverflow.com/questions/1221559/count-vs-count1

Collectives™ on Stack Overflow

Access sql combine SELECT and COUNT function

3 Answers 3

Query:

Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Query:

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related