CUBE:-IT GENERATES SUBTOTAL FOR ALL POSSIBLE COMBINATION OF GROUPED COLUMNS.
GROUPING SETS:-GENERATES SUMMARY INFORMATION AT THE CHOOSEN LEVEL,WITHOUT INCLUDING ALL THE ROWS PRODUCED BY REGULAR GROUP BY OPERATION.
GROUPING,GROUPING_ID,GROUP_ID :-HELPS TO CORRECTLY INTERPRET RESULTS GENERATED USING ROLLUP,CUBE AND GROUPING SETS. - select empno,ename,sum(sal),avg(sal) from emp group by cube(ename,empno);
-
-
select decode(grouping(empno),1,'all empno',empno) as no,empno,ename,sum(sal),avg(sal) from emp group by cube(ename,empno);
-
-
-
select decode(grouping(empno),1,'all empno',empno) as no,empno,ename,sum(sal),avg(sal) from emp group by cube(ename,empno)
-
-
select empno,ename,avg(sal) from emp group by grouping sets(empno,ename)
The value from GROUPING(JOB) will be 1 or 0 depending on whether or not the values for SAL are due to the GROUP BY or the CUBE. If the results are due to the CUBE, the value will be 1, otherwise it will be 0.
---------------------------------------------------- - select deptno,
-
job,
-
case grouping(deptno)||grouping(job)
-
when '00' then 'TOTAL BY DEPT AND JOB'
-
when '10' then 'TOTAL BY JOB'
-
when '01' then 'TOTAL BY DEPT'
-
when '11' then 'GRAND TOTAL FOR TABLE'
-
end category,
-
sum(sal) sal
-
from emp
-
group by cube(deptno,job)
-
order by grouping(job),grouping(deptno)
Few more sample query
==================
Sample #1
=========== - select deptno, job, sum(sal) sal,
-
grouping(deptno) deptno_subtotals,
-
grouping(job) job_subtotals
-
from emp
-
group by cube(deptno,job)
Sample #2
=========== - select ename,
-
job,
-
case when job = 'CLERK'
-
then 1 else 0
-
end as is_clerk,
-
case when job = 'SALESMAN'
-
then 1 else 0
-
end as is_sales,
-
case when job = 'MANAGER'
-
then 1 else 0
-
end as is_mgr,
-
case when job = 'ANALYST'
-
then 1 else 0
-
end as is_analyst,
-
case when job = 'PRESIDENT'
-
then 1 else 0
-
end as is_prez
-
from emp
-
order by 2
Sample #3
=========== - select max(case deptno when 10 then ename end) d10,
-
max(case deptno when 20 then ename end) d20,
-
max(case deptno when 30 then ename end) d30,
-
max(case job when 'CLERK' then ename end) clerks,
-
max(case job when 'MANAGER' then ename end) mgrs,
-
max(case job when 'PRESIDENT' then ename end) prez,
-
max(case job when 'ANALYST' then ename end) anals,
-
max(case job when 'SALESMAN' then ename end) sales
-
from (
-
select deptno, job, ename,
-
row_number()over(partition by deptno order by empno) rn
-
from emp
-
) x
-
group by rn
Performing Aggregations over Different Groups/Partitions Simultaneously
================================================== ==== - select ename,deptno, count(*)over(partition by deptno) deptno_cnt, job,
-
count(*)over(partition by job) job_cnt,
-
count(*)over() total from emp
Performing Aggregations over a Moving Range of Values
========================================== - select hiredate,
-
sal,
-
sum(sal)over(order by hiredate
-
range between 90 preceding
-
and current row) spending_pattern
-
from emp e
Pivoting a Result Set with Subtotals
============================ - select mgr,
-
sum(case deptno when 10 then sal else 0 end) dept10,
-
sum(case deptno when 20 then sal else 0 end) dept20,
-
sum(case deptno when 30 then sal else 0 end) dept30,
-
sum(case flag when '11' then sal else null end) total
-
from (
-
select deptno,mgr,sum(sal) sal,
-
cast(grouping(deptno) as char(1))||
-
cast(grouping(mgr) as char(1)) flag
-
from emp
-
where mgr is not null
-
group by rollup(deptno,mgr)
-
) x
-
group by mgr
0  3712  Sign in to post your reply or Sign up for a free account.
Similar topics
by: Stephen C. Waterbury |
last post by:
This seems like it ought to work, according to the
description of reduce(), but it doesn't. Is this
a bug, or am I missing something?
Python 2.3.2 (#1, Oct 20 2003, 01:04:35)
on linux2
Type...
|
by: bher2 |
last post by:
gud day.
please help me. im working right now on a case study that will
retrieve/produce a simple report on sql. my problem is I dont know how
to pivot queries like in access. please help me....
|
by: Gert v O |
last post by:
Can someone help me parsing this ms-access PIVOT sql-statement to a
ms-sql-server sql-statement?
Many thanks in advance
TRANSFORM Count(KlantenStops.id) AS AantalVanid
SELECT...
|
by: FiveFootUnder |
last post by:
Hi. Ow. I have a bruised forehead from banging my head against a brick wall and would really appreciate some help here. As long as it's not a suggestion that I shouldn't be trying this!!
I'm...
|
by: debasisdas |
last post by:
This article contains some of the tips for PIVOTING the recordset (output of the query) . PIVOTING is mainly used for reporting purpose.
Displaying the total number of employees department wise....
|
by: debasisdas |
last post by:
Displaying Histogram-Horizontal
===========================
select deptno,lpad('*',count(*),'*') as cnt from emp group by deptno
Histogram-Vertical
===============
select row_number(...
|
by: debasisdas |
last post by:
Displaying Histogram-Horizontal
===========================
select deptno,lpad('*',count(*),'*') as cnt from emp group by deptno
Histogram-Vertical
===============
select row_number(...
|
by: Orbie |
last post by:
Hi all,
I have the following SQL Server query which retrieves rows i'm interested in:
Select Temp_Product_ID, Dept_Name, Section_Name, Commodity_Name, Product_Description, Rank, Guide_Price,...
|
by: MrMob |
last post by:
Plz teach me to do that...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
