D'oh! That's not exactly answering your original question is it?
This one will count all the titles with non alphabetic first letters as a single block: SELECT GAMES.name,count(a.L) AS lCount FROM GAMES, (SELECT LEFT(GAMES.name,1) AS L FROM GAMES) a WHERE ASC(LEFT(GAMES.name,1)) = ASC(a.L) OR (ASC(a.L) BETWEEN 0 AND 65) OR (ASC(a.L) BETWEEN 91 AND 96) OR (ASC(a.L) > 122) GROUP BY GAMES.name Spike Stephen Milligan Team Macromedia - ColdFusion Co-author 'Reality Macromedia ColdFusion MX: Intranets and Content Management' http://spikefu.blogspot.com > -----Original Message----- > From: Spike [mailto:[EMAIL PROTECTED]] > Sent: 21 November 2002 20:20 > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] Counting titles that do not start > with letter using SQL > > > ok, > > If you're using Access 2000 you should be able to do something like > this: > > SELECT GAMES.name,count(L) AS lCount > FROM GAMES INNER JOIN (SELECT LEFT(name,1) AS L > FROM GAMES) a ON LEFT(GAMES.name,1) = a.L > GROUP by GAMES.name > > Which will give you a column called lcount telling you how > many records have the same first letter as the value for name > in the current row. > > That's assuming your table is called games, and the column > you want to sort on is called name. > > Spike > > Stephen Milligan > Team Macromedia - ColdFusion > Co-author 'Reality Macromedia ColdFusion MX: Intranets and > Content Management' http://spikefu.blogspot.com > > > -----Original Message----- > > From: Giles Roadnight > > [mailto:[EMAIL PROTECTED]] > > Sent: 21 November 2002 19:21 > > To: [EMAIL PROTECTED] > > Subject: RE: [ cf-dev ] Counting titles that do not start > > with letter using SQL > > > > > > Access 2000 > > > > Have got it working now but not very neatly. > > > > Thanks for the help everyone. > > > > > > Giles Roadnight > > http://giles.roadnight.name > > > > > > -----Original Message----- > > From: Spike [mailto:[EMAIL PROTECTED]] > > Sent: 21 November 2002 18:09 > > To: [EMAIL PROTECTED] > > Subject: RE: [ cf-dev ] Counting titles that do not start > > with letter using SQL > > > > Ah yes, > > > > Sorry mis-read the question... > > > > Did you mean to say you aren't on MSSQL? > > > > If not, which database are you using? > > > > Depending on the DB type, there may be a function that allows > > you to do a count based on the first letter. Also, if the DB > > supports sub-select statements, you should be able to do it. > > > > Spike > > > > Stephen Milligan > > Team Macromedia - ColdFusion > > Co-author 'Reality Macromedia ColdFusion MX: Intranets and > > Content Management' http://spikefu.blogspot.com > > > > > -----Original Message----- > > > From: Giles Roadnight > > > [mailto:[EMAIL PROTECTED]] > > > Sent: 21 November 2002 17:40 > > > To: [EMAIL PROTECTED] > > > Subject: RE: [ cf-dev ] Counting titles that do not start > > > with letter using SQL > > > > > > > > > Not quite what I want, don't have mysql either. > > > > > > Thanks anyway > > > > > > > > > Giles Roadnight > > > http://giles.roadnight.name > > > > > > > > > -----Original Message----- > > > From: Spike [mailto:[EMAIL PROTECTED]] > > > Sent: 21 November 2002 16:30 > > > To: [EMAIL PROTECTED] > > > Subject: RE: [ cf-dev ] Counting titles that do not start > > > with letter using SQL > > > > > > SELECT colname > > > FROM tablename > > > WHERE colname LIKE ('[^A]%') > > > > > > That will match anything except an 'A'. > > > > > > Spike > > > > > > Stephen Milligan > > > Team Macromedia - ColdFusion > > > Co-author 'Reality Macromedia ColdFusion MX: Intranets > and Content > > > Management' http://spikefu.blogspot.com > > > > > > > -----Original Message----- > > > > From: Giles Roadnight > > > > [mailto:[EMAIL PROTECTED]] > > > > Sent: 21 November 2002 17:17 > > > > To: [EMAIL PROTECTED] > > > > Subject: [ cf-dev ] Counting titles that do not start with > > > > letter using SQL > > > > > > > > > > > > I am looping through a query of titles and I want to output the > > > > results per letter in two columns so under A there would be two > > > > columns of titles starting with A. To do this I need > the count of > > > > records beginning with A which I have done like this: > > > > > > > > <cfquery name="qry_getCurrentLettercount" dbtype="query"> > > > > select count(gameid) as lettercount > > > > from qry_getgames > > > > where name like '#request.currentLetter#%' > > > > </cfquery> > > > > > > > > How though do I get the count of titles that do not start with a > > > > letter? > > > > > > > > I could do it like this: > > > > <cfquery name="qry_getCurrentLettercount" dbtype="query"> > > > > select count(gameid) as lettercount > > > > from qry_getgames > > > > where name not like 'A%' > > > > and name not like 'B%' > > > > ect... > > > > </cfquery> > > > > But I don't want to have to write that out 52 times (upper and > > > > lowercase) > > > > > > > > Also is there a way to make SQL non case sensitive so the first > > > > query will count upper and lower case? > > > > > > > > Thanks > > > > > > > > Giles Roadnight > > > > http://giles.roadnight.name > > > > > > > > > > > > > > > > -- > > > > ** Archive: > > > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > > > > > > > To unsubscribe, e-mail: > > > [EMAIL PROTECTED] > > > > For additional commands, e-mail: > [EMAIL PROTECTED] > > > > For human help, e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > > > > -- > > > ** Archive: > > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > > > > To unsubscribe, e-mail: > > [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > For human help, e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > -- > > > ** Archive: > > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > > > > To unsubscribe, e-mail: > > [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > For human help, e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > > > -- > > ** Archive: > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > To unsubscribe, e-mail: > [EMAIL PROTECTED] > > For additional commands, e-mail: > > [EMAIL PROTECTED] For human help, e-mail: > > [EMAIL PROTECTED] > > > > > > > > -- > > ** Archive: > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > To unsubscribe, e-mail: > [EMAIL PROTECTED] > > For additional commands, e-mail: > > [EMAIL PROTECTED] For human help, e-mail: > > [EMAIL PROTECTED] > > > > > > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > > > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
