To the index

SQL

Beginner

How can I avoid the "ColumnName is not part of an aggregate function" error message when using COUNT and SUM in a SQL statement?

If you use an aggregate function such as COUNT or SUM in a SQL statement and you are also SELECTing other columns, you must add GROUP BY and list all the columns that aren't SUMmed or COUNTed. For example

<cfquery datasource="#request.DSN#" name="Referers">
   SELECT COUNT(Record) AS RecordCount,
          URL,
          Target
     FROM Referers
    WHERE ...
 GROUP BY URL,
          Target
</cfquery>

to question

How can I refer to columns names with spaces in a SQL statement?

Use back-ticks to refer to the column names and then alias them so you can refer to them as something else:

  SELECT `First Name` AS FirstName,
         `Last-Name` AS LastName,
          PhoneExtension
     FROM Employees
ORDER BY `Last Name`,
         `First Name`

You can then refer to FirstName and LastName within your query-driven CFOUTPUT block.

to question

How do I get a list of tables contained in a database?

You can do this with some simple SQL, depending on the RDBMS.The following query will return an alphabetically sorted list of tables contained in your Microsoft Access database.

Access

<cfquery datasource="myDatasource" name="GetTables">
   SELECT MSysObjects.Name
     FROM MSysObjects
    WHERE MSysObjects.Flags = 0
      AND MSysObjects.Type = 1
 ORDER BY MSysObjects.Name
</cfquery>

Ingres

SELECT relname
  FROM iirelation

Oracle

SELECT table_name
  FROM user_tables

PostgreSQL

SELECT relname
  FROM pg_class

MS SQL Server

SELECT name
  FROM sysobjects
 WHERE type = 'U'

Informix

SELECT tabname
  FROM systables
 WHERE tabtype = 'T'

Sybase SQL Server

SELECT name
  FROM sysobjects
 WHERE type = 'U'

to question

I'm having problems with apostrophies and SQL statements. What's going on?

ColdFusion will automatically escape an apostrophe if it's in a variable value that you're inserting into a database. It escapes it by doubling it up from ' to ''. For exmple, if Var1 and Var2 contain apostrophies, you don't need to worry about escaping the following code.

INSERT INTO
MyTable (Field1, Field2)
 VALUES ('#Var1#', '#Var2#')

If you're doing the insert manually, you will have to double them up like this

INSERT INTO
MyTable (Field1, Field2)
 VALUES ('Fred''s e-commerce store', 'Rick''s ColdFusion tips')

If you want to prevent ColdFusion from automatically escaping your apostrophies, use the PreserveSingleQuotes() function

<cfset ChartTypes = "'Bar','Pie','Scatter'">

<cfquery datasource="#DSN#" name="GetChartTypes">
   SELECT *
     FROM Charts
    WHERE ChartType IN (PreserveSingleQuotes(#ChartTypes#));
</cfquery>

to question

What does the Data type mismatch in criteria expression error message mean?

Probably because your variable isn't a numeric type. If it's character, you'll need quotes around it:

WHERE SomeField = '#YourVar#'

to question

What's faster, COUNT(*) or COUNT(somefield)?

If you use Count(*) it returns the total number of rows in a table If you use Count(ID) it returns the number of non-NULL entries in the field ID.

Count(*) is faster as it doesn't have to check to see if there are any NULLs anywhere.

Making an index on the field speeds up the Count(), but it's never as fast as Count(*).

to question

Where can I find a list of reserved words?

http://support.microsoft.com/support/kb/articles/q109/3/12.asp Access reserved words

SQL Server reserved words can be found in the SQL Server Books Online.

to question

Where can I find online SQL tutorials or guidance?

http://psyberspace.net/coldfusion/fundamentals/databases/index.cfm

to question