SQL AVG() with ROUND(), group by

AVG() with ROUND() and group by

In this page, we are going to discuss the usage of SQL ROUND() along with the SQL AVG() function. The SQL ROUND() is used to round the value up to a specific decimal places.

The GROUP BY clause with aggregate function makes the result within a group.

Examples:

Sample table: customer

To get the average of 'outstanding_amount' as a whole number that means rounded by zero from the 'customer' table, the following SQL statement can be used:

SELECT ROUND(AVG(outstanding_amt) ,0) AS "Rounded Avg."
FROM customer;

Output:

Rounded Avg.
------------
        7600

SQL AVG() with group by

Sample table: orders

To get the data of 'agent_code', average of 'advance_amount' for each agent from the 'orders' table with the following condition -

1. each agent_code must be in a group,

the following SQL statement can be used:

SELECT agent_code, AVG(advance_amount)
FROM orders GROUP BY agent_code;

Output:

AGENT_CODE AVG(ADVANCE_AMOUNT)
---------- -------------------
A004                       525
A002                       500
A007                       250
A009                       100
A011                       450
A012                       225
A010                       740
A013                      1600
A001                       200
A008                      1100
A006                       300
A005                1033.33333
A003                       500

SQL AVG() with ROUND() and group by

Sample table: customer

To get the data of 'agent_code', sum of 'opening_amt' with a heading 'Op. Amt' and average of 'outstanding_amount' as a whole number i.e. rounded zero (0) with a heading 'Rou. Avg.' for each agent from the 'customer' table with the following condition -

1. each agent should come in a group,

the following SQL statement can be used :

SELECT agent_code, SUM (opening_amt) AS "Op. Amt.", 
ROUND(AVG(outstanding_amt),0) AS "Rou. Avg." 
FROM customer 
GROUP BY agent_code;

Output:

AGENT_CODE   Op. Amt.  Rou. Avg.
---------- ---------- ----------
A002            22000      10667
A004            25000       8000
A007            16000      10500
A009             6000       5000
A011             5000       8000
A012             5000       3000
A010            22000       9333
A001             8000      12000
A008            13000       5000
A006             8000       4500
A005            19000       6000
A003            12000       7500

SQL AVG() with ROUND(), rounded upto nearest negative value

In the following we are going to discuss, how SQL ROUND() along with the SQL AVG() function can be used to round the value upto a specific decimal places. Rounding can be done upto nearest negative or positive places from the decimal ( . ).

Example:

Sample table: customer

To get the average of '(879.254/2)' with a heading 'My Avg' and the average of '(879.254/2)' rounded up to the 1 decimal place negative from the decimal point with a heading 'Rounded -1 Decimal Place' from the 'customer', the following SQL statement can be used:

SELECT AVG (879.254/2) AS "My Avg.", 
ROUND (AVG(879.254/2),-1) AS "Rounded -1 Decimal Place" 
FROM customer;

Output:

  My Avg. Rounded -1 Decimal Place
--------- ------------------------
  439.627                      440

SQL AVG() with ROUND(), rounded upto nearest positive value

Sample table: customer

To get the average of '(879.254/2)' with a heading 'My Avg' and the average of '(879.254/2)' rounded up to the 2 decimal place positive from the decimal point with a heading 'Rounded 2 Decimal Place' from the 'customer' table, the following SQL statement can be used :

SELECT AVG (879.254/2) AS "My Avg.", 
ROUND (AVG(879.254/2),2) AS "Rounded 2 Decimal Place" 
FROM customer;

Output:

   My Avg. Rounded 2 Decimal Place
---------- -----------------------
   439.627                  439.63

SQL AVG() with ROUND() as a whole number

Sample table: customer

To get the average of '(879.254/2)' and the average of '(879.254/2)' rounded up to the 0 th decimal place with a heading 'Rounded' from the 'customer' table, the following SQL statement can be used :

SELECT AVG (879.254/2), 
ROUND (AVG (879.254/2),0) AS "Rounded" 
FROM customer;

Output:

AVG(879.254/2)    Rounded
-------------- ----------
       439.627        440

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition

Here is a slide presentation of all aggregate functions.

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Avg Decimal Places Using Cast within and outside avg
Next: Max Function