Thanks spike, that's great
Giles Roadnight http://giles.roadnight.name -----Original Message----- From: Spike [mailto:[EMAIL PROTECTED]] Sent: 21 November 2002 19:57 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Counting titles that do not start with letter using SQL 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] -- ** 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]
