Tuesday, June 10, 2014

Multiple row function

MULTIPLE ROW FUNCTIONS / GROUP FUNCTIONS
----------------------------------------
Group functions operate on sets of rows OR GROUP OF ROW and gives one result per group.

** NULLS DO NOT PARTICIPATE IN GROUP FUNCTIONS **

SUM - will operate only on numeric datatype
AVG - will operate only on numeric datatype
MIN - will operate on all datatypes
MAX - will operate on all datatypes
COUNT - will operate on all datatypes
STDDEV - will operate only on numeric datatype
VARIANCE - will operate only on numeric datatype

SELECT SUM(sal) FROM emp;

SELECT SUM(ALL sal) FROM emp;

SELECT SUM(DISTINCT sal) FROM emp;

SELECT SUM(SAL), SUM(DISTINCT sal) FROM emp;

SELECT SUM(SAL), SUM(ALL sal) FROM emp;

SELECT AVG(comm) FROM emp;

SELECT 2200 / 4 FROM DUAL;

SELECT NVL(COMM,0) FROM EMP;

SELECT AVG(NVL(comm, 0)) FROM emp;

SELECT 2200 / 14 FROM dual;

SELECT AVG(COMM), AVG(NVL(comm, 0)) FROM emp;

SELECT distinct deptno,sal FROM emp;

SELECT sum(distinct sal) total, avg(distinct sal) average FROM emp;

SELECT SUM(job), AVG(job) FROM emp; -- gives error

SELECT MIN(sal) FROM emp;

SELECT MIN(ename) FROM emp;

SELECT MIN(hiredate) FROM emp;

SELECT MAX(sal) FROM emp;

SELECT MAX(ename) FROM emp;

SELECT MAX(hiredate) FROM emp;

SELECT COUNT(ename) FROM emp;

SELECT COUNT(comm) FROM emp;

SELECT COUNT(hiredate) FROM emp;

SELECT COUNT(*) FROM emp;

SELECT STDDEV(sal) FROM emp;

SELECT VARIANCE(sal) FROM emp;

SELECT DEPTNO, SAL FROM EMP ORDER BY DEPTNO;

SELECT SUM(sal) FROM emp GROUP BY deptno;

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

SELECT deptno, SUM(sal) FROM emp;  -- will give error

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno ORDER BY DEPTNO;

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno ORDER BY SUM(sal);

SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno, job ORDER BY deptno;

SELECT deptno, job, SUM(sal) FROM emp; -- gives error

SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno; -- gives error

SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno, job;

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

SELECT deptno, SUM(sal) FROM emp WHERE SUM(sal) > 10000 GROUP BY deptno; /* we
cannot use group function in the where clause */

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 10000;

SELECT deptno, SUM(sal) FROM emp HAVING SUM(sal) > 10000 GROUP BY deptno ;

SELECT deptno, SUM(sal) FROM emp WHERE job<>'PRESIDENT' GROUP BY deptno
HAVING SUM(sal) > 8000 ORDER BY DEPTNO;

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING deptno<>30;

SELECT deptno, SUM(sal) FROM emp WHERE deptno <> 30 GROUP BY deptno;

SELECT deptno, sal FROM emp ORDER BY deptno;

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno ORDER BY deptno;

SELECT deptno, SUM(sal) FROM emp GROUP BY ROLLUP(deptno) ORDER BY deptno;

SELECT deptno, job, SUM(sal) FROM emp GrOUP BY ROLLUP(deptno, job) ORDER BY deptno;

DEPTNO, JOB
DEPTNO
GRAND TOTAL

n+1=2+1=3

SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno, job ORDER BY deptno;

deptno, job
deptno
job
grand total

SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO, JOB) ORDER BY DEPTNO;

SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO;

SELECT DEPTNO, SUM(SAL) FROM EMP WHERE SUM(SAL)>10000 GROUP BY DEPTNO; -- GIVE ERROR

SELECT DEPTNO, SUM(SAL) FROM EMP  GROUP BY DEPTNO HAVING SUM(SAL)>10000;

SELECT DEPTNO, SUM(SAL) FROM EMP WHERE JOB<>'PRESIDENT' GROUP BY DEPTNO HAVING SUM(SAL)>10000;


SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB);


SELECT job, ROUND(AVG(sal)) FROM emp GROUP BY job;

SELECT job, SUM(sal) FROM emp GROUP BY job;

SELECT deptno, job, SUM(sal) FROM emp
GROUP BY deptno;       -- gives error                           

SELECT deptno, job, SUM(sal) FROM emp
GROUP BY deptno, job
ORDER BY deptno;

SELECT deptno, SUM(sal) FROM emp
WHERE deptno IN(10,20)
GROUP BY deptno;

SELECT deptno, SUM(sal) FROM emp WHERE SUM(sal)> 9000 GROUP BY deptno; -- gives error

SELECT deptno, SUM(sal) FROM emp WHERE deptno IN(10,20,30)
GROUP BY deptno HAVING SUM(sal)> 9000;

SELECT MIN(SAL), MAX(SAL), round(AVG(SAL),2), SUM(SAL), count(empno) FROM EMP;

SELECT MIN(HIREDATE), MAX(HIREDATE) FROM EMP;

SELECT MIN(ENAME), MAX(ENAME) FROM EMP;

SELECT COUNT(*) FROM EMP;

SELECT COUNT(MGR) FROM EMP;

SELECT COUNT(COMM) FROM EMP;

SELECT COUNT(NVL(COMM,0)) FROM EMP;

SELECT COUNT(DISTINCT DEPTNO) FROM EMP;

SELECT COUNT(DISTINCT DEPTNO), SUM(DISTINCT SAL), SUM(SAL) FROM EMP;

select null from emp;

select 1 from emp;

select count(1) from emp;

select count(null) from emp;

desc emp;

SELECT AVG(SAL) FROM EMP WHERE DEPTNO=10;

SELECT AVG(COMM) FROM EMP WHERE DEPTNO=30;

SELECT COUNT(COMM) FROM EMP WHERE DEPTNO IN (10,20);

SELECT SUM(COMM) FROM EMP WHERE DEPTNO IN (10,20);

SELECT MAX(SAL) FROM EMP;

select max(sal) from emp where deptno=10
union
select max(sal) from emp where deptno=20
union
select max(sal) from emp where deptno=30;

SELECT MAX(SAL) FROM EMP WHERE DEPTNO IN(10,20,30) GROUP BY DEPTNO;

select 10 dno, max(sal) msal from emp where deptno=10
union
select 20, max(sal) from emp where deptno=20
union
select 30, max(sal) from emp where deptno=30;

SELECT DEPTNO, MAX(SAL) FROM EMP WHERE DEPTNO IN(10,20,30) GROUP BY DEPTNO;

CREATING GROUPS OF DATA:

SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO;

SELECT JOB, COUNT(*) FROM EMP GROUP BY JOB;

SELECT DEPTNO, JOB, COUNT(*) FROM EMP GROUP BY DEPTNO, JOB order by deptno;

SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)<5 ORDER BY 1;

SELECT DEPTNO, COUNT(*) FROM EMP WHERE count(*)>3 GROUP BY DEPTNO; -- gives error

SELECT COUNT(*) FROM EMP WHERE count(*)>3;

SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>3;

SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>=3;

SELECT DEPTNO, COUNT(*) FROM EMP WHERE DEPTNO IN (10,20) GROUP BY DEPTNO;

SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING deptno IN (10,20);

SELECT DEPTNO, COUNT(*) FROM EMP WHERE sal<3000 GROUP BY DEPTNO;

SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY deptno HAVING sal<3000; -- gives error


No comments:

Post a Comment