SQL - As part of an aggregate function ERROR

[Guest]

I am trying to extract the first digit of a list of numbers and related count
of those numbers. In the following SQL statement I get the error "You are
trying to use mid(F1,1,1) as part of an aggregate function error. I am not
sure what that means and can't find it in the help files.

SELECT mid(F1,1,1), COUNT(mid(F1,1,1)) FROM [" & strRange1 & "]

Any ideas? I have tried using "Group by" witht the same error. Anytime
Count is included in any form I seem to get that error.

Thanks,
Dave

 

[Sharad Naik]

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

Sharad

 

[Guest]

Try:

Sql="select distinct(fchar),sum(Freq) from ( "
Sql = Sql & "select mid(f1,1,1) as fchar, 1 as Freq from [" & strRange1 &
"] ) a group by fchar

 

[onedaywhen]

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.

--

 

[Guest]

This works! The thing I can't do is replace the GROUP BY with fchar. I must
use GROUP BY MID(F1,1,1). Is this because fchar is not defined yet when the
SQL statement is performed?

Thanks for the help.

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.

 

[onedaywhen]

The thing I can't do is replace the GROUP BY with fchar. I must
use GROUP BY MID(F1,1,1). Is this because fchar is not defined yet when the
SQL statement is performed?

According to the ANSI specs what *should* happen is that when a column
alias is used in the SELECT clause, that alias is the *only* way of
referring to the column/expression (e.g. in the ORDER BY and GROUP BY
clauses) because the underlying tables should not be subsequently used
i.e. the newly-build cursor should be used instead.

Jet patently does not follow the spec because the opposite is true i.e.
you cannot use the alias, you have to use the original column name (or
full expression) and this usage should be illegal. [I'm not sure how
Jet works under the covers but perhaps it cannot optimize the
expression so it *must* go back to the original tables?]

So you *should* be able to use the alias but with Jet SQL you can't.
FWIW SQL Server is similarly non-compliant [and is similarly file-based
under the covers].

Jamie.

--

 

[Guest]

Thanks! I tried over and over with different forms. Until I stopped using
the alias, it would not work. Now I understand. Thanks again, this should
help with other potential issues.

Dave

The thing I can't do is replace the GROUP BY with fchar. I must
use GROUP BY MID(F1,1,1). Is this because fchar is not defined yet when the
SQL statement is performed?

According to the ANSI specs what *should* happen is that when a column
alias is used in the SELECT clause, that alias is the *only* way of
referring to the column/expression (e.g. in the ORDER BY and GROUP BY
clauses) because the underlying tables should not be subsequently used
i.e. the newly-build cursor should be used instead.

Jet patently does not follow the spec because the opposite is true i.e.
you cannot use the alias, you have to use the original column name (or
full expression) and this usage should be illegal. [I'm not sure how
Jet works under the covers but perhaps it cannot optimize the
expression so it *must* go back to the original tables?]

So you *should* be able to use the alias but with Jet SQL you can't.
FWIW SQL Server is similarly non-compliant [and is similarly file-based
under the covers].

Jamie.