/*Group by Functions
GROUP or AGGREGATE Functions
Group or Aggregate functions process similary group of rows and returns one value
1.MAX()
2.MIN()
3.SUM()
4.AVG()
5.COUNT()
6.COUNT(*)
7.count(1)
1.MAX()
It returns maximum value.
Syntax:
MAX(colname)
Eg:
*/
SELECT MAX(SAL) FROM EMP;
SELECT MAX(HIREDATE) FROM EMP; --It will give us the most recent record which has the recent date.
select hiredate from emp order by hiredate desc;
SELECT MAX(ENAME) FROM EMP; --WARD
select ename from emp order by ename desc;
select max(sal) from emp;--5000
select max(hiredate) from emp;--12-JAN-83
select max(ename) from emp;--WARD
select max(empno) from emp;--7934
select max(job) from emp;--SALESMAN
select max(comm) from emp;--1540
select max(deptno) from emp;--30
select comm from emp;
/*
MIN()
"It returns minimum(smallest) value
Syntax:
MIN(colname) */
SELECT MIN(SAL) FROM EMP; --800
SELECT MIN(ENAME) FROM EMP;--ADAMS
select min(sal) from emp;--800
select min(hiredate) from emp;--17-DEC-80
select min(ename) from emp;--ADAMS
select min(empno) from emp;--7369
select min(job) from emp;--ANALYST
select min(comm) from emp;--0
select min(deptno) from emp;--10"
/*SUM()
"It returns total
Syntax:
SUM(colname)
Eg:
*/
SELECT SUM(SAL) FROM EMP; --30145
/*
select sum(hiredate) from emp;--error
00932. 00000 - ""inconsistent datatypes: expected %s got %s"
select sum(ename) from emp;--error
01722. 00000 - ""invalid number"*/ --need how to use sum function for hiredates
select sum(empno) from emp;--108172
/*
select sum(job) from emp;--error
01722. 00000 - ""invalid number"
*/--need to check how to use sum for string values (check note)
select sum(comm) from emp;--2200
select sum(deptno) from emp;--310
Note: Sum() function cannot be applied on Character and date columns. It can be applied only on number columns
--Q: Round total sal to neareset hundreds?
select sal from emp;
select sum(sal) from emp;
select round(sum(sal),-2) from emp;
29000 29050 29100
29000
select sum(sal) from emp;30145
select round(sum(sal),-2) from emp;--30100
--Q:After rounding display total sal with thousand seperator and currency symbol?
select to_char(round(sum(sal),-2),'L99G999') amt_curr from emp;
$29,000
select sum(sal) from emp; 30145
select round(sum(sal),-2) from emp;--30100
select to_char(round(sum(sal),-2),'L99G999D99') amount_USD from emp;-- $30,100.00
Q:Calculate total sal including comm?
"select sum(sal+comm) from emp;--7000
select sum(sal+nvl(comm,0)) from emp;--31225
select sum(sal+nvl(comm,null)) from emp; --7000
select sum(sal) from emp;--29025"
/* AVG()
It calculate average value
Syntax:
AVG(colname) */
Eg:
SELECT AVG(SAL) FROM EMP;
--Q:Round avg(sal) to lowest integer?
select floor(avg(sal)) from emp;
2153
--AVG () function cannot be applied on characters,date columns. It can be applied on only numeric columns
/* COUNT()
Returns no of values present in a column.
Syntax count(colname)
*/
select count(sal) from emp;--14
select count(hiredate) from emp;--14
select count(ename) from emp;--14
select count(empno) from emp;--14
select count(job) from emp;--14
select count(comm) from emp;--4
select count(deptno) from emp;--14
/*
COUNT(*)
It counts all rows, regardless of the values in the rows. It even counts rows with NULL values
Meaning it checks for all the columns and rows count
It performs slow
*/
/* Count(1)
It counts all rows, but instead of evaluating the entire row, it evaluates the constant 1 for each row.
Meaning it checks for single column row count */
select count(1) from emp;
select count(*) from emp;
select *from emp;
--Q:How many employees joined in 1981 year?
select ename,hiredate from emp;
select sysdate from dual;
select extract(year from hiredate) year from emp;
select count(1) total_number_of_emp ,extract(year from sysdate) as YR from emp where extract(year from hiredate)=1987 group by extract(year from hiredate);
SELECT COUNT(1)
FROM EMP
WHERE HIREDATE LIKE '%81';
select count(1) from emp where hiredate like '%81';
select count(1) from emp where hiredate like '%87';
--Q: Find no of employees joined on sunday?
SELECT COUNT(1)
FROM EMP
WHERE TO_CHAR(HIREDATE,'fmDAY') ='SUNDAY';
select to_char(HIREDATE,'fmDAY') from emp;
/*
--Notes
1.Group functions are not allowed in where clause and they are allowed only in Select,Having clauses.
*/
select
*
from
emp
where
sal=max(sal);--error:group function is not allowed here
select
*
from
dept
where
count(*)>3;- -error
select count(*) from dept group by deptno having count(*)>=2;
select *from dept;
Comments
Post a Comment