Difference between cube and rollup

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 24 Jan 2005 6:19 am

Good morning Guys could someone explain to me what the difference between cube and rollup and to serving both and when should I use, If not too much trouble could you send me example.Thanks David
david ribeiro guilherme
Location: Catanduva - SP

David Ribeiro Guilherme
Programador
ribeiro131@hotmail.com

Poston Mon, 24 Jan 2005 9:51 am

CUBE is an extension to the group by clause, which groups the selected rows based on values of all possible combinations for each row, returning one row with the summary information for each group. Can use to generate the so-called CROSS-TABULATION VALUES
Code: Select all
SELECT DECODE(GROUPING(dname), 1, 'All Departments',
dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
FROM emp, dept
WHERE dept.deptno = emp.deptno
SELECT and subquery
11-106 SQL Reference
GROUP BY CUBE (dname, job);


DNAME JOB Total Empl Average Sa
--------------- --------- ---------- ----------
ACCOUNTING CLERK 1 15600
ACCOUNTING MANAGER 1 29400
ACCOUNTING PRESIDENT 1 60000
ACCOUNTING All Jobs 3 35000
RESEARCH ANALYST 2 36000
RESEARCH CLERK 2 11400
RESEARCH MANAGER 1 35700
RESEARCH All Jobs 5 26100
SALES CLERK 1 11400
SALES MANAGER 1 34200
SALES SALESMAN 4 16800
SALES All Jobs 6 18800
All Departments ANALYST 2 36000
All Departments CLERK 4 12450
All Departments MANAGER 3 33100
All Departments PRESIDENT 1 60000
All Departments SALESMAN 4 16800
All Departments All Jobs 14 24878.5714
ROLL UP Used for example to generate subtotals
Code: Select all
SELECT DECODE(GROUPING(dname), 1, 'All Departments',
dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
FROM emp, dept
WHERE dept.deptno = emp.deptno
GROUP BY ROLLUP (dname, job);
DNAME JOB Total Empl Average Sa
--------------- --------- ---------- ----------
ACCOUNTING CLERK 1 15600
ACCOUNTING MANAGER 1 29400
ACCOUNTING PRESIDENT 1 60000
ACCOUNTING All Jobs 3 35000
RESEARCH ANALYST 2 36000
RESEARCH CLERK 2 11400
RESEARCH MANAGER 1 35700
RESEARCH All Jobs 5 26100
SALES CLERK 1 11400
SALES MANAGER 1 34200
SALES SALESMAN 4 16800
SALES All Jobs 6 18800
All Departments All Jobs 14 24878.5714
+/-ae: wink:
MuLtAnI
Location: Videira - SC



  • See also
    Replies
    Views
    Last Post


        Return to SQL

        Who is online

        Users browsing this forum: No registered users and 2 guests