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