SQL CAST() inside AVG() for decimal value
CAST() function inside AVG() function
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 to 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 etc. |
| specified_format | Specified format. |
Example :
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 the average of 'advance_amount' up to 2 decimal places form the 'orders' table, the following SQL statement can be used:
SELECT CAST(AVG(advance_amount) AS DECIMAL(10,2)) -- Selects the average value of the 'advance_amount' column, casted to a decimal with precision 10 and scale 2
FROM orders; -- Specifies the 'orders' table as the source of data
Explanation:
- SELECT CAST(AVG(advance_amount) AS DECIMAL(10,2)): This is the main part of the SQL query. It uses the AVG() function to calculate the average value of the 'advance_amount' column in the 'orders' table. The result of AVG() is then casted to a decimal data type with a precision of 10 digits and a scale of 2 digits using the CAST() function. This ensures that the average value is rounded to two decimal places.
- 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.
Output:
CAST(AVG(ADVANCE_AMOUNT)ASDECIMAL(10,2))
----------------------------------------
629.17
SQL AVG() using CAST() inside the AVG()
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', number of customer, average of 'opening_amt' rounded upto two decimal with a heading 'SQLAVG' for each agent from the customer table with the following condition -
1. each 'agent_code' should come in a group
the following SQL statement can be used :
SELECT agent_code, COUNT(*), -- Selects the agent code, counts the number of rows for each agent, and calculates the average opening amount for each agent
AVG(CAST(opening_amt AS DECIMAL(12,2))) AS SQLAVG -- Calculates the average opening amount for each agent, rounded to two decimal places
FROM customer -- Specifies the 'customer' table as the source of data
ff -- This seems to be a typo or an error in the SQL query. It should be removed.
GROUP BY agent_code; -- Groups the result set by the agent code
Explanation:
- SELECT agent_code, COUNT(*),: This is the main part of the SQL query. It selects three columns: 'agent_code', counts the number of rows for each 'agent_code', and calculates the average opening amount for each 'agent_code'.
- AVG(CAST(opening_amt AS DECIMAL(12,2))) AS SQLAVG: This part calculates the average opening amount for each 'agent_code'. The CAST() function is used to convert the 'opening_amt' column to a decimal data type with a precision of 12 digits and a scale of 2 digits, ensuring accurate arithmetic operations. The AVG() function then calculates the average value of the 'opening_amt' column for each 'agent_code'. The result is rounded to two decimal places. The alias 'SQLAVG' is assigned to this calculated average.
- FROM customer: This specifies the source of the data for the query, which is the 'customer' table.
- ff: This part seems to be a typo or an error in the SQL query. It should be removed as it is not valid syntax.
- 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 will be applied separately for each unique value in the 'agent_code' column.
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+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |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', 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(*), -- Selects the agent code and counts the number of rows for each agent
CAST(AVG(opening_amt) AS DECIMAL(12,2)) AS SQLAVG -- Calculates the average opening amount for each agent and casts it to a decimal with precision 12 and scale 2
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, COUNT(*),: This is the main part of the SQL query. It selects three columns: 'agent_code', counts the number of rows for each 'agent_code', and calculates the average opening amount for each 'agent_code'.
- CAST(AVG(opening_amt) AS DECIMAL(12,2)) AS SQLAVG: This part calculates the average opening amount for each 'agent_code' using the AVG() function. The result is then casted to a decimal data type with a precision of 12 digits and a scale of 2 digits using the CAST() function. This ensures that the calculated average is rounded to two decimal places. The alias 'SQLAVG' is assigned to this calculated average.
- FROM customer: This specifies the source of the data for the query, which is the 'customer' 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 will be applied separately for each unique value in the 'agent_code' column.
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+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |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', 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 a heading 'SQLAVG',
the following SQL statement can be used:
SELECT agent_code, COUNT(*), -- Selects the agent code and counts the number of rows for each agent
AVG(opening_amt) AS SQLAVG -- Calculates the average opening amount for each agent
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, COUNT(*): This part of the query selects the 'agent_code' column and counts the number of rows for each 'agent_code'. The COUNT(*) function counts all rows in each group.
- AVG(opening_amt) AS SQLAVG: Here, the query calculates the average opening amount for each 'agent_code' using the AVG() function. The calculated average is given the alias 'SQLAVG'.
- FROM customer: This specifies the source of the data for the query, which is the 'customer' 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 will be applied separately for each unique value in the 'agent_code' column.
Relational Algebra Expression:
Relational Algebra Tree:
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: despatchDES_NUM DES_DATE DES_AMOUNT ORD_NUM ORD_DATE ORD_AMOUNT AGENT_CODE ---------- --------- ---------- ---------- --------- ---------- ---------- D002 10-JUN-08 2000 200112 30-MAY-08 2000 A007 D005 19-OCT-08 4000 200119 16-SEP-08 4000 A010 D001 12-JAN-08 3800 200113 10-JUN-08 4000 A002 D003 25-OCT-08 900 200117 20-OCT-08 800 A001 D004 20-AUG-08 450 200120 20-JUL-08 500 A002 D006 24-JUL-08 4500 200128 20-JUL-08 3500 A002
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 -- Calculates the average of the difference between 'des_date' and 'ord_date' columns, and aliases the result as 'average_despatch_days'
FROM despatch; -- Specifies the 'despatch' table as the source of data
Explanation:
- SELECT AVG(des_date - ord_date) AS average_despatch_days: This part of the query calculates the average of the difference between the 'des_date' and 'ord_date' columns. It subtracts the 'ord_date' from the 'des_date' for each row, resulting in the difference in days between the dispatch date and order date. The AVG() function then calculates the average of these differences. The result is aliased as 'average_despatch_days'.
- FROM despatch: This specifies the source of the data for the query, which is the 'despatch' 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 'despatch' table.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
AVERAGE_DESPATCH_DAYS
---------------------
-11
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 function
Next: Avg with round, group by
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql/aggregate-functions/avg-decimal-places-using-cast-within-avg.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics