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 SUBQUERIES
- 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]
- Hospital Database
- Employee Database
- 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
SQL: Tips of the Day
ROW_NUMBER() in MySQL
SELECT t0.col3 FROM table AS t0 LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3 WHERE t1.col1 IS NULL;
Ref : https://bit.ly/3VX3Jzv
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises