AVG in Sql - Float number problem

Question

SELECT     AVG(variable) AS Expr1, SUM(variable) AS Expr2
FROM       ......

result for AVG is 2, but it is not true, it must be 2.95. What is the problem, any idea?

What is the type of variable? I guess that it is an integer. Right? — luc
– luc, Commented Jul 9, 2009 at 7:53

user110714 · Accepted Answer · 2009-07-09 07:50:49Z

66

Try

Select
    AVG(Cast(variable as Float)),
    SUM(variable)
From
    Table

answered Jul 9, 2009 at 7:50

user110714

Sign up to request clarification or add additional context in comments.

2 Comments

Pouya BCD Over a year ago

I would suggest using decimal instead of float. See this: stackoverflow.com/questions/1056323/…

jlh Over a year ago

@PouyaBCD Without knowing the precise use-case you can't just suggest to use decimals instead of floats. There exist perfectly valid use-cases for floats/doubles where using decimals would not work well (for example when you need huge ranges, for example numbers from 1e-18 to 1e+18).

Della Smith · Accepted Answer · 2024-05-24 21:08:18Z

0

SELECT AVG(CAST(age)) AS average_age FROM Customer C JOIN Purchase P1 USING (customer_key) JOIN Product P USING (product_key) WHERE P.name = 'Smartwatch' AND YEAR(date) = 2024

answered May 24, 2024 at 21:08

Della Smith

1

2 Comments

SmellyCat Over a year ago

The original query does not mention table names, let alone joins. Did you mean to include those? I also notice that the CAST operation does not specify a type, as opposed to the 2009 answer, which specifies FLOAT. Does this work?

Abra Over a year ago

Please learn how to use markdown to format your posts.

Collectives™ on Stack Overflow

AVG in Sql - Float number problem

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related