SQL AVG() to 2 decimals

SQL AVG() to 2 decimals

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

SQL AVG() to 2 Decimals

When you query a database for an average value, the result often carries more precision than your application logic actually requires. In many cases, displaying four or five decimal places for a percentage lift looks unprofessional and complicates downstream calculations. If you are working with raw SQL data returned via PHP and MySQLi, as shown in your snippet, you likely found that applying CAST inside the query string didn't yield the expected truncation.

The core issue here is a misunderstanding of how SQL rounding functions interact with type casting. Simply wrapping the column or the function in a CAST(... AS DECIMAL(5,2)) works perfectly fine and is generally the preferred approach because it handles the calculation within the database engine before the data even reaches your application layer.

However, if you attempted to cast inside the AVG() function specifically like this:

SELECT CAST(AVG(app_tests.test_resultPercent) AS DECIMAL(5,2)) FROM app_tests

it should theoretically work, but there are nuances depending on the data types of your column and how MySQLi handles floating-point numbers versus fixed-point decimals.

If casting inside the aggregate function isn't behaving as expected, consider using ROUND() instead. While CAST changes the precision and scale of the result, ROUND(value, 2) explicitly rounds the number to two decimal places. This is often more robust when dealing with floating-point arithmetic where minor representation errors can cause values like 17.4534 to become 17.454 or 17.453 depending on internal storage quirks.

Here is a revised version of your SQL query that leverages ROUND:

SELECT ROUND(AVG(app_tests.test_resultPercent), 2) AS average_percent FROM app_tests;

By aliasing the result as average_percent, you avoid echoing the raw column name in your output, which makes your HTML rendering cleaner. When you fetch this row in PHP, accessing $row3['average_percent'] will yield a string that is ready for display without needing further manipulation.

It is important to distinguish between truncation and rounding. If your business logic requires strict truncation (always dropping digits beyond the second decimal), SUBSTRING() combined with a cast could be used, but this is rarely necessary for financial or analytical metrics where standard rounding is acceptable. Using ROUND ensures that 17.456 becomes 17.46, which aligns with standard accounting practices.

Another consideration is the data type of your column test_resultPercent. If it is stored as an INT, dividing by a constant might be needed before averaging, but since you are using AVG() directly on it, MySQL likely treats it as a floating-point value internally. If the column contains NULL values, AVG() automatically ignores them, which is usually the desired behavior. However, if your dataset frequently contains nulls and you need a default value instead of an average calculation skipping rows, you might need to handle that logic separately or use COALESCE on the result.

In a Laravel context, while native SQL queries allow direct access to these functions via $conn, using Eloquent often provides a cleaner abstraction layer. You can achieve the same rounding behavior within an Eloquent query by accessing the raw query builder:

$average = DB::raw('ROUND(AVG(app_tests.test_resultPercent), 2)');
$results = App\Models\AppTest::selectRaw($average)->get();

This approach keeps your logic consistent whether you are querying via raw statements or ORM models. If you find yourself needing to format numbers for display in a Laravel view, remember that PHP's number_format() function is also an option if you prefer handling the formatting on the application side rather than in the database. However, offloading this to SQL reduces network traffic and keeps heavy lifting where it belongs: the database server.

Ultimately, the choice between casting and rounding depends on whether you need exact precision or a standardized display format. For most reporting scenarios involving percentages, ROUND(AVG(...), 2) provides the cleanest, most predictable results without introducing unnecessary complexity into your query structure.

Our network