Very Computer

select id, extract(month from Date_reading) as MM, sum(Reading)
from MyDB
group by extract(month from Date_reading)

in this case I get "Capability not supported".

using:
group by MM           I get "MM"  "invalid field name".

using:
group by 2               I get "Capability not supported".

group by extract(month from Date-reading)

Just a guess.

select extract (month from Date_reading) as MM, sum (Reading) as Reading_Sum
from MyDB
group by extract (month from Date_reading)

Since you're using Local SQL you may still get the capability-not-supported
error, because Local SQL may not be able to group on a function result. In
that case, break the query into two parts and create a view. See "View" in
the index of LocalSql.hlp in the BDE directory. The first query would be
this:

select extract (month from Date_reading) as MM, Reading
from MyDB

Save this query as, e.g., MonthReadings.sql.

Your second query looks like this:

select MM, sum (Reading) as Reading_Sum
from "MonthReadings.sql"
group by MM

I don't know the purpose of your query, but you might need to select and
group by the year as well as month.

  Richard

Quote:> This is my requirement: In a table each record represents a meter reading.
I
> want to know the month total. I tried the following SQL statement:

> select id, extract(month from Date_reading) as MM, sum(Reading)
> from MyDB
> group by extract(month from Date_reading)

> in this case I get "Capability not supported".

> using:
> group by MM           I get "MM"  "invalid field name".

> using:
> group by 2               I get "Capability not supported".

> I am using the BDE and MyDB is a dBase table. May be this is the problem,
> but: There any way to get what I want ?

"Richard Biffl" <usenet a t removvethiisphrraseblacklettersoftware.com> ha

Quote:> You can't include the id in the result set, because which id would
represent
> all the records in the month? So in standard SQL you would use:

> select extract (month from Date_reading) as MM, sum (Reading) as
Reading_Sum
> from MyDB
> group by extract (month from Date_reading)

> Since you're using Local SQL you may still get the