How to Retrieve Decimals When Rounding an Average in SQL

How do I retrieve decimals when rounding an average in SQL

The average will have the same data type as the values, so cast the values:

SELECT ROUND(AVG(CAST(column_name AS FLOAT)), 2) FROM [database].[dbo].[table]

SQL AVG() to 2 decimals

Perhaps the ROUND() function would work?

$sql3 = "SELECT ROUND(AVG(app_tests.test_resultPercent),2) FROM app_tests"; 

EDIT

Your PHP looks strange. You should either alias the average function as a column and access it by that name, or access it by index. Something like

echo "<h3>".$row3[0];

How to get average in only 2 decimals using SQL Server 2014?

I tried the following syntax and it worked. I used a mix of suggestions from @GordonLinoff and @ThorstenKettner. I have upvoted both their answers.

,FORMAT (AVG( CAST((TimeToAnswer) AS Decimal (10,2))), '#########0.00') [Average]

I am now getting the following results with automatic rounding up or down as necessary.

Year    Month   Day      Average
2017    10       1       0.47
2017    10       2       1.51
2017    10       3       1.64
2017    10       4       0.89
2017    10       5       0.54
2017    10       6       0.64

Rounding off to two decimal places in SQL

You could cast your result as numeric(x,2). Where x <= 38.

select
    round(630/60.0,2),
    cast(round(630/60.0,2) as numeric(36,2))

Returns

10.500000    10.50

SQL Calculating decimal average instead of int

Your problem is that COUNT returns an integer, so dividing two COUNT values is an integer division, which will return an integer. Adding a multiplication by 1.0 makes the operation floating point, which then returns the expected result:

SELECT 
       ROUND(1.0*COUNT(DISTINCT(session_id))/COUNT(DISTINCT(user_id)) ,2 ) average_sessions_per_user
FROM Activity
WHERE activity_date >= DATEADD(day, -30, '2019-07-27')

Demo on SQLFiddle

Note also that your date inside DATEADD needs to be enclosed in quotes (see the above query and demo).

Rounding within avg() over () clause not working - SQL Server

Run it like this:

Select round(avg(amount*1.0) over (order by date asc ROWS BETWEEN 6 PRECEDING AND current row),2) average_amount

Sometimes it just needs *1.0

How to round an average to 2 decimal places in PostgreSQL?

PostgreSQL does not define round(double precision, integer). For reasons @Mike Sherrill 'Cat Recall' explains in the comments, the version of round that takes a precision is only available for numeric.

regress=> SELECT round( float8 '3.1415927', 2 );
ERROR:  function round(double precision, integer) does not exist

regress=> \df *round*
                           List of functions
   Schema   |  Name  | Result data type | Argument data types |  Type  
------------+--------+------------------+---------------------+--------
 pg_catalog | dround | double precision | double precision    | normal
 pg_catalog | round  | double precision | double precision    | normal
 pg_catalog | round  | numeric          | numeric             | normal
 pg_catalog | round  | numeric          | numeric, integer    | normal
(4 rows)

regress=> SELECT round( CAST(float8 '3.1415927' as numeric), 2);
 round 
-------
  3.14
(1 row)

(In the above, note that float8 is just a shorthand alias for double precision. You can see that PostgreSQL is expanding it in the output).

You must cast the value to be rounded to numeric to use the two-argument form of round. Just append ::numeric for the shorthand cast, like round(val::numeric,2).

---

If you're formatting for display to the user, don't use round. Use to_char (see: data type formatting functions in the manual), which lets you specify a format and gives you a text result that isn't affected by whatever weirdness your client language might do with numeric values. For example:

regress=> SELECT to_char(float8 '3.1415927', 'FM999999999.00');
    to_char    
---------------
 3.14
(1 row)

to_char will round numbers for you as part of formatting. The FM prefix tells to_char that you don't want any padding with leading spaces.

MySQL AVG function gives more decimal than expected

Double is not an exact type, it being a more precise version of float. You should use an exact numeric column type, if you want exact precision. From the MySQL documentation:

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.

You could get around this on the presentation side by rounding, but use an exact numeric type for a more long term solution.

How to return a number with two decimal places in SQL Server without it automatically rounding

You can use floor() and integer division:

select floor(8.23897666 * 100) / 100

Or better yet, use round() with a non-0 third argument:

select round(8.23897666, 2, 1)

---

Related Topics