Hi,

I've noticed an apparent change in behaviour in recent V2.02 builds (I am currently using 2.02 b10).

The following SQL statement returns an error:

SELECT ColumnName, SUM(ColumnName) FROM TableName

ElevateDB Error #700 An error was found in the statement at line 1 and column 8 (Invalid expression "ColumnName" found, the necessary GROUP BY clause is missing)

I've checked the documentation and it states that the GROUP BY clause can be used in a SUM or COUNT statement, not that it is required. It also seems like a limitation rather than a
useful restriction.

Am I imagining things and was it always like that? Is it a bug or deliberate?

If it is deliberate, is there a reason for the limitation and are there any workarounds? I've tried GROUP BY TRUE / FALSE and while it avoids the error the results are not what I would
expect (all the rows present, the sum of all the rows as the second column).  

Thanks,

-Alex

Alexander

> I've noticed an apparent change in behaviour in recent V2.02 builds (I am
currently using 2.02 b10).
>
> The following SQL statement returns an error:
>
> SELECT ColumnName, SUM(ColumnName) FROM TableName
>
> ElevateDB Error #700 An error was found in the statement at line 1 and
column 8 (Invalid expression "ColumnName" found, the necessary GROUP BY
clause is missing)
>
> I've checked the documentation and it states that the GROUP BY clause can
be used in a SUM or COUNT statement, not that it is required. It also seems
like a limitation rather than a
> useful restriction.
>
> Am I imagining things and was it always like that? Is it a bug or
deliberate?

I think you are imagining things <bg>

If you want the sum of all items on a single row omit ColumnName ie

SELECT SUM(ColumnName) FROM TableName

John

Alexander


Its how it works. Its also a difference in interpretation of the manual. I had a discussion with Tim over his definition of the word "optional". As is often the case he came up with examples that meant he won

Roy Lambert [Team Elevate]

Alexander,

<< I've noticed an apparent change in behaviour in recent V2.02 builds (I am
currently using 2.02 b10).

The following SQL statement returns an error:

SELECT ColumnName, SUM(ColumnName) FROM TableName >>

The current version is correct, so if it behaved differently in the past
(which I'm not aware of), then it was incorrect.  You can include constants
in the SELECT list along with aggregates:

SELECT "Hello", SUM(ColumnName) FROM TableName

but not other column references like your example does.  Your example is
completely ambiguous if you think about it - from which row should the
ColumnName be returned ?

--
Tim Young
Elevate Software
www.elevatesoft.com