w3resource logo


>Sql avg and Cast inside Avg

SQL cast() inside avg() for decimal value

rating Average rating 7 out of 10. Total 8 users rated.

<<PreviousNext>>

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

Sql Avg() with decimal value

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

Sql Avg() using Cast() inside the Avg()

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

Sql Avg() using Cast() outside the Avg()

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

Sql Avg() with count()

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

Sql Avg() on date

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

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>>