Group by - part of a field
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=1555
Printed Date: 28 March 2026 at 11:06pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Group by - part of a field
Posted By: pedalcars
Subject: Group by - part of a field
Date Posted: 02 April 2003 at 2:37pm
|
Part of a statement is...
strSQL = strSQL & "GROUP BY tblResults.Car_Number " For reasons too complicated to report here, Car_Number is a decimal number with 2dp. Under one condition, I want to "Group By" the exact number (i.e., as per the above code); otherwise I want to group by Car_Number trimmed to 1dp. Does there exist a function that would let me write: strSQL = strSQL & "GROUP BY (function to trim to 1dp)tblResults.Car_Number " ? If not, I'll have to come up with something else, but if possible the above would provide a neat solution to my particular problem. Ta ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels 
|
Replies:
Posted By: michael
Date Posted: 02 April 2003 at 3:35pm
|
If I understand right the car number is something like 5.32 and you want to group by that number but sometimes you want to just by 5.3 ? as there are cars called 5.31 5.32 5.33 and so forth? Let me know if that is what you want to do! ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: pedalcars
Date Posted: 02 April 2003 at 4:28pm
|
Bang on, yes.
------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels
|
Posted By: michael
Date Posted: 02 April 2003 at 5:11pm
|
Well you got a little problem there I think. If you'd use SQL Server you could just execute something like select carid from table group by left(carid,3) this unfortunately does not work in access though. I would create an extra field in access and just enter the part you need for that grouping or create a temp table, select everything into there using left (assuming the carid is always below 10 otherwise use another function) and then query that temp table. Let me know if you get stuck doing that... ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: pedalcars
Date Posted: 03 April 2003 at 2:38am
|
Thanks for the help. It occurred late last night that even if I could do what I wanted, it would only have given me a partial solution to what I wanted to do.
Thus I now have an additional table; so far this seems to be working and able to cope with whatever demands I'm making of it. Thanks again. Edit additional: It also occurs that the left(carid,3) would stumble on cars with double-figure numbers. I thought I was asking a lot...  ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels
|
Posted By: michael
Date Posted: 03 April 2003 at 7:24am
|
That is why I said if there are double digitnumber in there you shoulduse seomthing like formatnumber() or so. ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: pedalcars
Date Posted: 03 April 2003 at 8:41am
|
Sorry, missed that bit first time round. D'oh! ------------- http://www.pedalcars.info/ - www.pedalcars.info The most fun on four wheels
|