Group by case : GROUP BY « Select Query « Oracle PL / SQL

Oracle PL / SQL
1. Aggregate Functions
2. Analytical Functions
3. Char Functions
4. Constraints
5. Conversion Functions
6. Cursor
7. Data Type
8. Date Timezone
9. Hierarchical Query
10. Index
11. Insert Delete Update
12. Large Objects
13. Numeric Math Functions
14. Object Oriented Database
15. PL SQL
16. Regular Expressions
17. Report Column Page
18. Result Set
19. Select Query
20. Sequence
21. SQL Plus
22. Stored Procedure Function
23. Subquery
24. System Packages
25. System Tables Views
26. Table
27. Table Joins
28. Trigger
29. User Previliege
30. View
31. XML
Java
Java Tutorial
Java Source Code / Java Documentation
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Oracle PL / SQL » Select Query » GROUP BY 
Group by case
   
SQL> create table emp(
  2           emp_no                 integer         primary key,
  3           lastname               varchar2(20)    not null,
  4           firstname              varchar2(15)    not null,
  5           midinit                varchar2(1),
  6           street                 varchar2(30),
  7           city                   varchar2(20),
  8           state                  varchar2(2),
  9           zip                    varchar2(5),
 10           shortZipCode                   varchar2(4),
 11           area_code              varchar2(3),
 12           phone                  varchar2(8),
 13           salary                 number(5,2),
 14           birthdate              date,
 15           startDate              date,
 16           title                  varchar2(20),
 17           dept_no                integer,
 18           mgr                    integer,
 19           region                 number,
 20           division               number,
 21           total_sales            number
 22  );

Table created.

SQL> -- emp Table Inserts:
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values
  2                      (1,'Z','Joy','R','Ave','New York','NY','12122','2333','212','200-1111','12-nov-1976','President');

row created.

SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu
es
  2                      (2,'X','Lucy','J','Street','New York','NY','43552','6633','212','234-4444',7.75,'21-mar-1976','1-feb-1994','Sales Manager',2,1,100,10,40000);

row created.

SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu
es
  2                      (3,'Y','Jordan','E','Drive','New York','NY','76822','8763','212','222-2222',7.75,'14-feb-1963','15-mar-1995','Sales Clerk',2,2,100,10,10000);

row created.

SQL>
SQL> select
  2  case when salary between and 10 then 'Salary 06-10'
  3       when salary between 11 and 15 then 'Salary 11-15'
  4       when salary between 16 and 20 then 'Salary 16-20'
  5       when salary between 21 and 25 then 'Salary 21-25'
  6       else 'Salary OOR'
  7       end
  8       AS bucket,
  9  count(*num_emps
 10  from emp
 11  group by
 12  case when salary between and 10 then 'Salary 06-10'
 13       when salary between 11 and 15 then 'Salary 11-15'
 14       when salary between 16 and 20 then 'Salary 16-20'
 15       when salary between 21 and 25 then 'Salary 21-25'
 16       else 'Salary OOR'
 17       end
 18  /

BUCKET         NUM_EMPS
------------ ----------
Salary 06-10          2
Salary OOR            1

rows selected.

SQL>
SQL> drop table emp;

Table dropped.

SQL>

   
    
    
  
Related examples in the same category
1. Use avg, sum, max and count functions with group
2. Using the GROUP BY Clause
3. Must include a nonaggregate column in the SELECT list in the GROUP BY clause
4. Use group by and avg
5. GROUP BY may be used on a column without the column name appearing in the result set
6. Grouping at Multiple Levels: group by more than one column
7. Column sequence in the group by impacts the ordering
8. Using the ORDER BY Clause to Sort Groups
9. You don't have to include the columns used in the GROUP BY clause in your SELECT clause
10. GROUP BY and HAVING clauses
11. Using a Column Multiple Times in a GROUP BY Clause
12. timing and auto tracing a select statement with group
13. Group joined tables
14. Group by course name then by begin date
15. Group and count employeem and display only if its count is more than 4
16. Count all employees by even/odd employee id
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.