Oracle displaying and aggregating data

What are aggregate functions in SQL? What are those functions?

Define, syntax and an example with output for:
Aggregate functions in SQL are used to perform calculation on data. These functions are inbuilt in SQL and return a single value.

Define, syntax and an example with output for:

SUM function

SUM function returns the sum or addition of all NOT NULL values of a column. For e.g. I have a Table employee with the fields id, name, salary and I want the sum of all salaries, I can use SUM function as shown
SELECT SUM(emp_salary) from employee;
Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 63,000

AVG (average) function

AVG function returns the average of all NOT NULL values of a column. For e.g. I have a Table employee with the fields id, name, salary and I want the average of all salaries, I can use AVG function as shown
SELECT AVG(emp_salary) from employee;
Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 21,000.

COUNT function

COUNT function returns the number of rows or values of a table. For e.g. I have a Table employee with the fields id, name, salary and I want the count of all rows, I can use COUNT function as shown
SELECT COUNT(*) from employee;

Max and Min function.

MAX function returns the largest value of a column in a table. For e.g. I have a Table employee with the fields id, name, salary and I want the maximum salary of an employee, I can use MAX function as shown
SELECT MAX(emp_salary) from employee;
Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 22,000

MIN function returns the smallest value of a column in a table. For e.g. I have a Table employee with the fields id, name, salary and I want the minimun salary of an employee, I can use MIN function as shown
SELECT MIN(emp_salary) from employee;
Hence, if my column emp_salary has values 20,000, 22,000, 21,000; the output will be 20,000.

Using ROLLUP to aggregate data in SQL

ROLLUP in SQL allows you summarize your data and view. I have a Table company with the fields dept, branch, number of employees and following is the data.
DeptBranchNumber
DevSeatle2000
AnalystSeatle1000
TesterSt Louis400
DevSt Louis100
Analystboston250
devboston500
Testerboston560

On using a ROLLUP query,
SELECT Dept, Branch, SUM(Number) as Number FROM company GROUP BY dept,branch WITH ROLLUP
Will give results of state wise inventory of each branch.
SQL number functions - use to manipulate retrieved numbers
SQL number functions - ABS(number), CEIL(number), FLOOR(number), MOD(number, divisor), POWER(number, power), SIGN(number)...
SQL single-row character munipulation functions
SQL single-row character munipulation functions - CONCAT(string1, string2), INITCAP(string), LENGTH(string), LPAD(string, #,padding_char), RPAD(string, #,padding_char), LTRIM(string,searchString), RTRIM(string,searchString)...
SQL single-row date functions
SQL single-row date functions - SYSDATE, ADD_MONTHS(Date, months_to_add), LAST_DAY(Date), MONTHS_BETWEEN(Date1, Date2)...
Post your comment

You may like