John Jason Jordan wrote:
On Wed, 12 Mar 2008 00:09:40 -0400
Drew Jensen <[EMAIL PROTECTED]> dijo:
In other words you want the count for each GROUP of records.
SELECT Count( * ) FROM "MainBank"
GROUP BY ( SUBSTR( "QuestionCode", 1, 9 ) )
Thanks, but Base chokes on that:
"Syntax error in SQL expression."
Oddly, it works if I change GROUP BY to ORDER BY:
SELECT Count( * ) FROM "MainBank"
ORDER BY ( SUBSTR( "QuestionCode", 1, 9 ) )
Although then it just gives me one answer, "7,349."
Does Base not understand GROUP BY? Might there be some variant syntax
required? GROUP ON instead of GROUP BY or some such?
LOL - yes, it understands grouping...but...with versions prior to 2.4
the Base query analyzer doesn't accept the SUBSTR ( sorry I've been
switched to 2.4 for a while now and forgot about that ) function in the
group by clause.
Under versions < 2.4 you will need to turn Escape Processing off to run
this query. Which also means you will need to alias the return column.
So - change the query to
SELECT Count(*) AS "Count" FROM "MainBank"
ORDER BY ( SUBSTR( "QuestionCode", 1, 9 ) )
With the query designer in SQL view click on the 'Run SQL direct' tool button (
the letters SQL with a green checkmark ) and it will run fine.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
