w3resource logo


>Sql avg and Cast inside Avg

SQL cast() inside avg() for decimal value

<<PreviousNext>>

Seondary Nav

Description

The SQL AVG() function returns the average value with default decimal places. The CAST() is used to increase or decrease the decimal places of a value. The CAST() function is much better at preserving the decimal places when converting decimal and numeric data types. The 'AS DECIMAL' followed by the format specification is used with CAST() for making a numeric value in a specific decimal place value.

Syntax

CAST [ expression]
AS [data_type] [specified_format];

Parameters

Name Description
expression Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
data_type CHAR(), VARCHAR(), DECIMAL(), FLOAT e.t.c.
specified_format Specified format.

Example

Sample table : orders

To get the data the average of 'advance_amount' upto 2 decimal places form the 'orders' table, the following SQL statement can be used :

SELECT CAST(AVG(advance_amount) AS DECIMAL(10,2))
FROM orders;

Output

CAST(AVG(ADVANCE_AMOUNT)ASDECIMAL(10,2))
----------------------------------------
                                  629.17

SQL avg() using cast() inside the avg()

Sample table : customer

To get the data of 'agent_code', number of customer, average of 'opening_amt' rounded upto two decimal with a heading 'SQLAVG' for each agent from the customer table with following condition -

1. each 'agent_code' should come in a group

the following SQL statement can be used :

SELECT agent_code, COUNT(*),
AVG(CAST(opening_amt AS DECIMAL(12,2))) AS SQLAVG
FROM customer
ff GROUP BY agent_code;

Output

AGENT_CODE   COUNT(*)     SQLAVG
---------- ---------- ----------
A002                3 7333.33333
A004                3 8333.33333
A007                2       8000
A009                1       6000
A011                1       5000
A012                1       5000
A010                3 7333.33333
A001                1       8000
A008                3 4333.33333
A006                2       4000
A005                3 6333.33333
A003                2       6000

SQL avg() using cast() outside the avg()

Sample table : customer

To get the data of 'agent_code', number of customer, average of 'opening_amt' rounded upto two decimal with a heading 'SQLAVG' for each agent from the customer table with following condition -

1. each 'agent_code' should come in a group,

the following SQL statement can be used :

SELECT agent_code, COUNT(*), 
CAST(AVG (opening_amt) AS DECIMAL (12,2)) AS SQLAVG 
FROM customer 
GROUP BY agent_code;

Output

AGENT_CODE   COUNT(*)     SQLAVG
---------- ---------- ----------
A002                3    7333.33
A004                3    8333.33
A007                2       8000
A009                1       6000
A011                1       5000
A012                1       5000
A010                3    7333.33
A001                1       8000
A008                3    4333.33
A006                2       4000
A005                3    6333.33
A003                2       6000

SQL avg() with count()

Sample table : customer

To get the data of 'agent_code', number of each agent and average of 'opening_amt' for each agent with an user defined heading 'SQLAVG' from the customer table with following conditions -

1. each agent must be in a group,

2. and average should come with an heading 'SQLAVG',

the following SQL statement can be used :

SELECT agent_code, COUNT( * ),
AVG( opening_amt ) AS SQLAVG
FROM customer
GROUP BY agent_code;

Output

AGENT_CODE   COUNT(*)     SQLAVG
---------- ---------- ----------
A002                3 7333.33333
A004                3 8333.33333
A007                2       8000
A009                1       6000
A011                1       5000
A012                1       5000
A010                3 7333.33333
A001                1       8000
A008                3 4333.33333
A006                2       4000
A005                3 6333.33333
A003                2       6000

SQL avg() on datetime

Sample table : despatch

To get the average of ('des_date' - 'ord_date') from the 'despatch' table, the following SQL statement can be used :

SELECT AVG (des_date - ord_date) AS average_despatch_days 
FROM despatch;

Output

AVERAGE_DESPATCH_DAYS
---------------------
                  -11

All Aggregate Functions

SQL Aggregate Functions, slide presentation

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.



<<PreviousNext>>

 

Looking for some other tutorial?