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+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
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." -- Calculates the average of 'outstanding_amt' and rounds it to 0 decimal places, aliasing the result as "Rounded Avg."
FROM customer; -- Specifies the 'customer' table as the source of data
Explanation:
- SELECT ROUND(AVG(outstanding_amt), 0) AS "Rounded Avg.": This part of the query calculates the average of the 'outstanding_amt' column in the 'customer' table using the AVG() function. The result is then rounded to 0 decimal places using the ROUND() function. The alias "Rounded Avg." is assigned to this calculated average.
- FROM customer: This specifies the source of the data for the query, which is the 'customer' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'customer' table.
Output:
Rounded Avg.
------------
7600
SQL AVG() with group by
Sample table: orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
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) -- Selects the agent_code and calculates the average advance_amount for each agent
FROM orders -- Specifies the 'orders' table as the source of data
GROUP BY agent_code; -- Groups the result set by the agent_code
Explanation:
- SELECT agent_code, AVG(advance_amount): This part of the query selects the 'agent_code' column and calculates the average 'advance_amount' for each agent. The AVG() function calculates the average value of the 'advance_amount' column within each group of rows sharing the same 'agent_code'.
- FROM orders: This specifies the source of the data for the query, which is the 'orders' table. The FROM keyword is used to indicate the table from which the data will be selected. In this case, it selects data from the 'orders' table.
- GROUP BY agent_code: This clause groups the result set by the 'agent_code' column. The GROUP BY clause is used to aggregate the rows based on the values in the 'agent_code' column. This means that calculations performed in the SELECT statement (such as the average calculation) will be applied separately for each unique value in the 'agent_code' column.
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+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
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, -- Selects the agent_code column
SUM(opening_amt) AS "Op. Amt.", -- Calculates the sum of opening_amt for each agent and aliases the result as "Op. Amt."
ROUND(AVG(outstanding_amt), 0) AS "Rou. Avg." -- Calculates the average of outstanding_amt for each agent, rounds it to 0 decimal places, and aliases the result as "Rou. Avg."
FROM customer -- Specifies the 'customer' table as the source of data
GROUP BY agent_code; -- Groups the result set by the agent_code
Explanation:
- SELECT agent_code: This part of the query selects the 'agent_code' column.
- SUM(opening_amt) AS "Op. Amt.": This calculates the sum of the 'opening_amt' column for each agent. The SUM() function aggregates the values of 'opening_amt' within each group of rows with the same 'agent_code'. The result is aliased as "Op. Amt.".
- ROUND(AVG(outstanding_amt), 0) AS "Rou. Avg.": This calculates the average of the 'outstanding_amt' column for each agent. The AVG() function computes the average value of 'outstanding_amt' within each group of rows with the same 'agent_code'. The ROUND() function then rounds this average to 0 decimal places. The result is aliased as "Rou. Avg.".
- FROM customer: This specifies the source of the data for the query, which is the 'customer' table.
- GROUP BY agent_code: This groups the result set by the 'agent_code' column. The GROUP BY clause is used to aggregate the rows based on the values in the 'agent_code' column. This means that calculations performed in the SELECT statement will be applied separately for each unique value in the 'agent_code' column.
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+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
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.", -- Calculates the average of the expression 879.254 divided by 2 and aliases the result as "My Avg."
ROUND(AVG(879.254 / 2), -1) AS "Rounded -1 Decimal Place" -- Calculates the average of the same expression, then rounds the result to the nearest ten (-1 decimal places) and aliases the result as "Rounded -1 Decimal Place"
FROM customer; -- Specifies the 'customer' table as the source of data
Explanation:
- SELECT AVG(879.254 / 2) AS "My Avg.": This part of the query calculates the average of the expression 879.254 / 2. The result of this expression is the average of 879.254 divided by 2. The AVG() function computes the average value of this expression across all rows in the 'customer' table. The result is aliased as "My Avg.".
- ROUND(AVG(879.254 / 2), -1) AS "Rounded -1 Decimal Place": This calculates the average of the same expression (879.254 / 2) as before. However, the result is then rounded to the nearest ten (-1 decimal places) using the ROUND() function. The ROUND() function rounds the average to the nearest integer when the second argument is negative. The result is aliased as "Rounded -1 Decimal Place".
- FROM customer: This specifies the source of the data for the query, which is the 'customer' table.
Output:
My Avg. Rounded -1 Decimal Place --------- ------------------------ 439.627 440
SQL AVG() with ROUND(), rounded upto nearest positive value
Sample table: customer+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
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.", -- Calculates the average of the expression 879.254 divided by 2 and aliases the result as "My Avg."
ROUND(AVG(879.254 / 2), 2) AS "Rounded 2 Decimal Place" -- Calculates the average of the same expression, then rounds the result to 2 decimal places and aliases the result as "Rounded 2 Decimal Place"
FROM customer; -- Specifies the 'customer' table as the source of data
Explanation:
- SELECT AVG(879.254 / 2) AS "My Avg.": This part of the query calculates the average of the expression 879.254 / 2. The result of this expression is the average of 879.254 divided by 2. The AVG() function computes the average value of this expression across all rows in the 'customer' table. The result is aliased as "My Avg.".
- ROUND(AVG(879.254 / 2), 2) AS "Rounded 2 Decimal Place": This calculates the average of the same expression (879.254 / 2) as before. However, the result is then rounded to 2 decimal places using the ROUND() function. The ROUND() function rounds the average to the nearest integer when the second argument is positive and specifies the number of decimal places to round to. The result is aliased as "Rounded 2 Decimal Place".
- FROM customer: This specifies the source of the data for the query, which is the 'customer' table.
Output:
My Avg. Rounded 2 Decimal Place ---------- ----------------------- 439.627 439.63
SQL AVG() with ROUND() as a whole number
Sample table: customer+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
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), -- Calculates the average of the expression 879.254 divided by 2
ROUND(AVG(879.254/2), 0) AS "Rounded" -- Calculates the average of the same expression, then rounds the result to 0 decimal places and aliases the result as "Rounded"
FROM customer; -- Specifies the 'customer' table as the source of data
Explanation:
- SELECT AVG(879.254/2): This part of the query calculates the average of the expression 879.254 / 2. The result of this expression is the average of 879.254 divided by 2. The AVG() function computes the average value of this expression across all rows in the 'customer' table.
- ROUND(AVG(879.254/2), 0) AS "Rounded": This calculates the average of the same expression (879.254 / 2) as before. However, the result is then rounded to 0 decimal places using the ROUND() function. The ROUND() function rounds the average to the nearest integer when the second argument is 0. The result is aliased as "Rounded".
- FROM customer: This specifies the source of the data for the query, which is the 'customer' table.
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.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Avg Decimal Places Using Cast within and outside avg
Next: Max Function
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics