Board index » delphi » Need help with type casting on numeric values from AVG aggregate functions

Need help with type casting on numeric values from AVG aggregate functions

Hi NG,

SQL statements below return a not rounded decimal value for xyz in a D4 DBGrid.

 SELECT
 Tbl1.StringField,  
 AVG(Tbl1.IntegerField) As xyz

 FROM Tbl1

 GROUP BY
 StringField

I tried:

 CAST(AVG(Tbl1.IntegerField) AS NUMERIC(9,2)) As xyz

with no success.

(BDE 5.01, Paradox, BDE configuration & Windows settings are all as they should).

How to round the average value??

Thanks
Arno

 

Re:Need help with type casting on numeric values from AVG aggregate functions


I had the same problem trying to use NUMERIC() or FLOAT() in a Query.
it looks like they don't work

I got around it with a double cast :

CAST (my_field AS FLOAT(5,2)) as my_new_field

will become

CAST (CAST(my_field *100 AS INTEGER) AS FLOAT)/100 as my_new_field,

not very elegant, but it works.

I wonder if NUMERIC or FLOAT function work with all kinds of databases.
If somebody knows their correct syntax, i'm interested too.

Denis

Re:Need help with type casting on numeric values from AVG aggregate functions


Thanks a lot Dennis, this one works for me:

 CAST(CAST(AVG(Tbl1."IntegerField")*100 AS Integer) AS Float) /100 As xyz

Arno

Quote
> I had the same problem trying to use NUMERIC() or FLOAT() in a Query.
> it looks like they don't work

> I got around it with a double cast :

> CAST (my_field AS FLOAT(5,2)) as my_new_field

> will become

> CAST (CAST(my_field *100 AS INTEGER) AS FLOAT)/100 as my_new_field,

> not very elegant, but it works.

> I wonder if NUMERIC or FLOAT function work with all kinds of databases.
> If somebody knows their correct syntax, i'm interested too.

> Denis

Other Threads