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

arno.garr...@gmx.de (Arno Garrels)

Delphi Developer
Sat, 11 Sep 2004 23:23:44 GMT

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

 
Denis Jeanro

Delphi Developer
Sun, 12 Sep 2004 04:36:56 GMT

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

Arno Garrel

Delphi Developer
Sun, 12 Sep 2004 13:40:42 GMT

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

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

2. Aggregate functions on dBase tables with NULL numeric values

3. Casting null values to numeric

4. Need help on Field type casting

5. AVG with CAST

6. PLEASE HELP WITH FUNCTION AVG

7. Need info: casting TimeStamp value

8. SQL Aggregate Function Help

9. Types and Type-casting ...

10. AVG instruction and float value