rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Nov 21st, 2006

 

Hi,

Aggregate functions are sum(), count(), avg(), max(), min()

like: 

select sum(sal) , count(*) , avg(sal) , max(sal) , min(sal) from emp;

analytical fuction differ from aggregate function

some of examples:

SELECT ename "Ename", deptno "Deptno", sal "Sal",
  SUM(sal)
    OVER (ORDER BY deptno, ename) "Running Total",
  SUM(SAL)
    OVER (PARTITION BY deptno
          ORDER BY ename) "Dept Total",
  ROW_NUMBER()
    OVER (PARTITION BY deptno
          ORDER BY ENAME) "Seq"
FROM emp
ORDER BY deptno, ename

 

SELECT * FROM (
  SELECT deptno, ename, sal, ROW_NUMBER()
  OVER (
    PARTITION BY deptno ORDER BY sal DESC
  ) Top3 FROM emp
)
WHERE Top3 <= 3

 

SELECT * FROM (
  SELECT deptno, ename, sal,
         DENSE_RANK()
  OVER (
    PARTITION BY deptno ORDER BY sal desc
  ) TopN FROM emp
)
WHERE TopN <= 3
ORDER BY deptno, sal DESC

Hope these examples will clear doubt about these functions

 

 

 

dev

• Dec 19th, 2006

 

Thank u Rampratap.

Dev

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 3rd, 2008

 

Group functions returns one result per each group of the result setWhere as analytical functions  returns multi rows per each group i.e. using analytical functions we may display group results along with individual rows.