Displaying Histogram-Horizontal
=========================== - select deptno,lpad('*',count(*),'*') as cnt from emp group by deptno
Histogram-Vertical
=============== - select row_number( )over(partition by deptno order by empno) rn,
-
case when deptno=10 then '*' else null end deptno_10,
-
case when deptno=20 then '*' else null end deptno_20,
-
case when deptno=30 then '*' else null end deptno_30
-
from emp
Displaying only the histogram
============================ - select max(deptno_10) d10,
-
max(deptno_20) d20,
-
max(deptno_30) d30
-
from (
-
select row_number( )over(partition by deptno order by empno) rn,
-
case when deptno=10 then '*' else null end deptno_10,
-
case when deptno=20 then '*' else null end deptno_20,
-
case when deptno=30 then '*' else null end deptno_30
-
from emp
-
) x
-
group by rn
-
order by 1 desc, 2 desc, 3 desc
Returning Non-GROUP BY Columns
============================ - select deptno,ename,job,sal,
-
case when sal = max_by_dept
-
then 'TOP SAL IN DEPT'
-
when sal = min_by_dept
-
then 'LOW SAL IN DEPT'
-
end dept_status,
-
case when sal = max_by_job
-
then 'TOP SAL IN JOB'
-
when sal = min_by_job
-
then 'LOW SAL IN JOB'
-
end job_status
-
from (
-
select deptno,ename,job,sal,
-
max(sal)over(partition by deptno) max_by_dept,
-
max(sal)over(partition by job) max_by_job,
-
min(sal)over(partition by deptno) min_by_dept,
-
min(sal)over(partition by job) min_by_job
-
from emp
-
) emp_sals
-
where sal in (max_by_dept,max_by_job,
-
min_by_dept,min_by_job)
Using ROLLUP to display total
============================= - select case grouping(job)
-
when 0 then job
-
else 'TOTAL'
-
end job,
-
sum(sal) sal
-
from emp
-
group by rollup(job)
ROLLUP (10g):- TO GENERATE TOTALS AND SUB-TOTALS IN THE SUMMERISED RESULT.IT CAN ONLY APPEAR IN A QUERY WITH A GROUP BY CLAUSE. - select empno,sum(sal) from emp group by rollup(empno);
-
-
select empno,ename,sum(sal) from emp group by rollup(empno,ename);
-
-
select empno,ename,sum(sal) from emp group by rollup(ename,empno);
-
-
select empno,ename,sum(sal),avg(sal) from emp group by rollup(ename,empno);
-
-
select grouping(empno),empno,ename,sum(sal),avg(sal) from emp group by rollup(ename,empno);
Also check Pivoting - 3 0 3796 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:
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...
|
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: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
| |