Re: SQL - As part of an aggregate function ERROR

From: onedaywhen (jamiecollins_at_xsmail.com)
Date: 01/06/05 

Date: 6 Jan 2005 02:14:06 -0800

Sharad Naik wrote:
> I think it should only COUNT(F1) and not COUNT(Mid(......etc.))
> Any how count of the first digits vand count of the total number will
be the
> same.

For similar reasons, I think it should be COUNT(*). In standard SQL,
COUNT(*) has special meaning and for most (all?) implementations of
SQL, COUNT(*) is optimized to count rows and will execute faster.
Specifying a column within the COUNT function forces the DBMS to
unnecessarily consult a data dictionary. Sure, it will count the null
rows but surely we aren't interested in null values in this query
anyhow and thus can be excluded in the WHERE clause.

Further to AA2e72E's post, although using an alias in the GROUP BY
clause is legal in SQL-92, I assume the OP is using Jet (Excel) which
doesn't comply with the ANSI standard in this way, so you have to use
the MID(...) expression instead.

Not that the MID function returns a TEXT data type, so if a numeric is
required it must be explicitly cast.

In summary, try this:

SELECT CLNG(MID(F1,1,1)) AS fchar,
COUNT(*) As Freq
FROM [Sheet1$]
WHERE F1 IS NOT NULL
  GROUP BY MID(F1,1,1);

Jamie. 

--