Board index » delphi » SQL query: SELECT SUM(time) ...

Stephan Heitmeyer

Delphi Developer
Sun, 26 Mar 2006 19:30:41 GMT

SQL query: SELECT SUM(time) ...

I need to access tables where fields have (quite unfortunately) been named
"time" etc.
This is outside my control.
The prototypical query for the TQuery would have to go something like:
SELECT activity,SUM(time) FROM xy WHERE name=... ORDER BY activity

This is rejected by BDE with "invalid use of keyword" referring to "time".
(On the other hand, MS Access does accept and process that same query.)

I cannot, obviously, use SELECT * and do the sum myself.
Is there any work-around without changing the field name?

Thanks a lot!

 
Gert Kell

Delphi Developer
Sun, 26 Mar 2006 20:46:52 GMT

Re:SQL query: SELECT SUM(time) ...


Quote
Stephan Heitmeyer wrote:
> I need to access tables where fields have (quite unfortunately) been named
> "time" etc.
> This is outside my control.
> The prototypical query for the TQuery would have to go something like:
> SELECT activity,SUM(time) FROM xy WHERE name=... ORDER BY activity

> This is rejected by BDE with "invalid use of keyword" referring to "time".
> (On the other hand, MS Access does accept and process that same query.)

> I cannot, obviously, use SELECT * and do the sum myself.
> Is there any work-around without changing the field name?

try
SELECT activity,SUM("time") FROM xy WHERE name=... ORDER BY activity

--
Gert

Gert Kell

Delphi Developer
Sun, 26 Mar 2006 23:18:25 GMT

Re:SQL query: SELECT SUM(time) ...


Quote
> SELECT activity,SUM("time") ...

> yields BDE message "Capability not supported".
> I should have mentioned I use D5 and the corresponding BDE version.
> Also, "ORDER BY" should really have been "GROUP BY".

The last one got trough a little bit too fast :(

Try
SELECT activity, SUM(xy."Time") FROM xy GROUP BY activity

--
Gert

Stephan Heitmeye

Delphi Developer
Sun, 26 Mar 2006 22:06:04 GMT

Re:SQL query: SELECT SUM(time) ...


Quote
"Gert Kello" <G...@gaiasoft.ee> wrote in message

news:3f83f91b@newsgroups.borland.com...

Quote
> Stephan Heitmeyer wrote:

> > I need to access tables where fields have (quite unfortunately) been
named
> > "time" etc.
> > This is outside my control.
> > The prototypical query for the TQuery would have to go something like:
> > SELECT activity,SUM(time) FROM xy WHERE name=... ORDER BY activity

> > This is rejected by BDE with "invalid use of keyword" referring to
"time".
> > (On the other hand, MS Access does accept and process that same query.)

> > I cannot, obviously, use SELECT * and do the sum myself.
> > Is there any work-around without changing the field name?

> try
> SELECT activity,SUM("time") FROM xy WHERE name=... ORDER BY activity

> --
> Gert

SELECT activity,SUM("time") ...

yields BDE message "Capability not supported".
I should have mentioned I use D5 and the corresponding BDE version.
Also, "ORDER BY" should really have been "GROUP BY".

Stephan

Stephan Heitmeye

Delphi Developer
Sun, 26 Mar 2006 22:39:30 GMT

Re:SQL query: SELECT SUM(time) ...


Quote
"Gert Kello" <G...@gaiasoft.ee> wrote in message

news:3f841c9f$1@newsgroups.borland.com...
Quote
> > SELECT activity,SUM("time") ...

> > yields BDE message "Capability not supported".
> > I should have mentioned I use D5 and the corresponding BDE version.
> > Also, "ORDER BY" should really have been "GROUP BY".

> The last one got trough a little bit too fast :(

> Try
> SELECT activity, SUM(xy."Time") FROM xy GROUP BY activity

> --
> Gert

Hi Gert

It looks quite logical now that I see it black on white. And it works!
Thank you very much!

Stephan

Other Threads

1. Problem with Query (select sum(*) ...)

2. QUERY: SELECT SUM

3. Select Sum(x)/Sum(y)

4. SQL, Sum selected

5. SQL SUM - Query Value out of Bounds

6. SQL query syntax using SUM and GROUP BY

7. sum in SQL query

8. SQL Query SUM problem ....

9. SQL: Value of SUM-QUERY

10. SQL: Value of SUM-QUERY