Quote:

> select

> CellNo, RecdAmount, RecdDate, sum(RecdAmount)

> from

> Rsd20

> where

> CellNo=:pCellNo

> group by

> CellNo

> I need to calculate a total hence I need the "group by" in order to be

> able to use aggregate functions. BDE is complaining that "Field in group

> by must be in the result set". Has anybody any idea wht's wrong?

When doing a GROUP BY, all the columns in the SELECT statement must either

be an aggregate (i.e. SUM, COUNT, AVG etc.), or be part of the GROUP BY.

You cannot just simply include RecdAmount and RecdDate - you must either

add the columns to the GROUP BY clause or perform an aggregation on them.

Simply put, let's say there are 10 rows for cellphone x. You want to group

by cellnumber - so you want a single row returned from the 10 rows for

cellphone x.

You tell the db engine to add (using the SUM) the recdamount of these 10

rows together and supply you with a single result. No problem. However,

what should the db engine do with the RecdAmount and RecdDate you

requested. There are 10 rows for that cell number and thus there are 10

values each for RecdDate and RecdAmount. You requested a single row to be

returned for cell number x. Should it give you the largest date, or the

smallest? Or should it add all the dates in these 10 rows together? Or

should it give you the average date? The db engine has no idea how to

supply you a single RecdDate or RecdDate from these 10 rows for cellphone x

- you need to tell it how to take these 10 rows and from these 10 values

derive/calculate a single value for RecdDate and RecdAmount.

I think you're trying to do something like:

select

CellNo, RecdDate, sum(RecdAmount)

from

Rsd20

where

CellNo=:pCellNo

group by

CellNo,

RecdDate

This will return the total amount per cell number per day.

regards,

Billy

PS. I suggest that you do the SQL tutorial that comes with Delphi's

Interbase. It will teach you the basics of SQL.