group by help

[ChewDoggie]

Hello all,

I have a query that looks something like this.

"select distinct racer.racerid, racer.lname, racer.fname, racer.amanum, racer.tempnumber, racer.city, IIF(registration.Status = 'Y' and registration.eventid = " & entId & ", 'YES', '') from registration RIGHT JOIN racer on (racer.racerid = registration.racerid)"

I'd like to group the result set by racerid. Can this be done? I don't know the syntax rules for "grouping".



Many Thanks!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[PHV]

And which aggregate function would you use with the registration fields ?

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

 

[ChewDoggie]

well, I suppose "count" would be good. I tried:

"select distinct racer.racerid, racer.lname, racer.fname, racer.amanum, racer.tempnumber, racer.city, IIF(count(registration.racerid) > 1 and registration.Status = 'Y' and registration.eventid = " & entId & ", 'YES', '') from registration RIGHT JOIN racer on (racer.racerid = registration.racerid)"

but I don't think it's recognizing the "count" in the "IF" statement as an aggregate function.



Many Thanks!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[ChewDoggie]

FYI..I'm just trying to eliminate duplicate racerid's in teh result set.

HTH


Many Thanks!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[PHV]

Something like this ?
"SELECT R.racerid, R.lname, R.fname, R.amanum, R.tempnumber, R.city" _
& ", Sum(IIf(L.Status = 'Y' And L.eventid = " & entId & ", 1, 0)) As [YES]" _
& " FROM racer R LEFT JOIN registration L ON R.racerid = L.racerid" _
& "GROUP BY R.racerid, R.lname, R.fname, R.amanum, R.tempnumber, R.city"


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

 

[PHV]

OOps, sorry for the typo (missing space):
"SELECT R.racerid, R.lname, R.fname, R.amanum, R.tempnumber, R.city" _
& ", Sum(IIf(L.Status = 'Y' And L.eventid = " & entId & ", 1, 0)) As [YES]" _
& " FROM racer R LEFT JOIN registration L ON R.racerid = L.racerid" _
& " GROUP BY R.racerid, R.lname, R.fname, R.amanum, R.tempnumber, R.city"


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

 

[ChewDoggie]

you are GOOD!


Many Thanks!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org