COunt and DISTINCT

[penguinspeaks]

Hey all. Got what I am hoping is a quick question for ya's.

("SELECT DISTINCT team2 FROM matches WHERE team1 = '" & var1 & "' AND team2 <> '" & var1 & "'")

This does give me the DISTINCT teams that are in that field. However, somehow, I would also like to have a COUNT of each DISTINCT team2

so it may return something like

team_A 5 meaning team A appeared 5 times in that field.
team_B 8 meaning team B appeared 8 times in that field and so on.

is this possible to do??
as you can see.. this is using sql in asp
hope someone can help.
thanks in advance
Jeff
Bam

 

[mordja]

Try

SELECT team2, count(team2)
FROM(
SELECT DISTINCT team2 FROM matches WHERE team1 = '" & var1 & "' AND team2 <> '" & var1 & "')
GROUP BY team2

Mordja

 

[PHV]

You may try this:
("SELECT A.team2,Count(*) FROM (SELECT team2 FROM matches WHERE team1='" & var1 & "' AND team2<>'" & var1 & "') A GROUP BY A.team2")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[penguinspeaks]

ok. i have done these, but excuse my ignorance here, but what are the results fields names going to be.

because i will be using it in a loop.
and need to be able to do something like this.

set team_count = Conn.Execute("SELECT A.team2,Count(*) FROM (SELECT team2 FROM matches WHERE team1='" & var1 & "' AND team2<>'" & var1 & "') A GROUP BY A.team2")

<%= team_count.fields.item("fieldname").Value%>

but i am unsure of what the team name field will be or the cont field.

 

[mordja]

If you use

SELECT team2, count(team2) as teamCount
FROM(
SELECT DISTINCT team2 FROM matches WHERE team1 = '" & var1 & "' AND team2 <> '" & var1 & "')
GROUP BY team2

You will get two fields: team2, teamCount

Mordja

 

[penguinspeaks]

ok... that works somewhat.
but it counted the number if distinct teams there, what i need is for it to count how many times a team appears in the team2 column.
example
RR is a team. so yes, i want that as the distinct team for the "team2" but the count needs to see how many times RR appears in that field.

what this does, is tell me how many times one team has played another. right now i am getting a result of 1, because distinctly, the name is only there once.
does this make sense??

 

[PHV]

Have you tried my suggestion, ie without the DISTINCT keyword and with the alias ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[penguinspeaks]

so what would the field names be with that?

 

[PHV]

Choose the alias you want in the first SELECT list, or use indice instead of field names:
team_count.fields(0) for team2
team_count.fields(1) for count of team2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[penguinspeaks]

ok cool.... this does give me what i want... thx a bunch
Jeff
Bam