Count all employees by even/odd employee id

    
SQL>
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;

Table created.

SQL> insert into emp values(1,'Tom','N',   'TRAINER', 13,date '1965-12-17',  800 , NULL,  20);

1 row created.

SQL> insert into emp values(2,'Jack','JAM', 'Tester',6,date '1961-02-20',  1600, 300,   30);

1 row created.

SQL> insert into emp values(3,'Wil','TF' ,  'Tester',6,date '1962-02-22',  1250, 500,   30);

1 row created.

SQL> insert into emp values(4,'Jane','JM',  'Designer', 9,date '1967-04-02',  2975, NULL,  20);

1 row created.

SQL> insert into emp values(5,'Mary','P',  'Tester',6,date '1956-09-28',  1250, 1400,  30);

1 row created.

SQL> insert into emp values(6,'Black','R',   'Designer', 9,date '1963-11-01',  2850, NULL,  30);

1 row created.

SQL> insert into emp values(7,'Chris','AB',  'Designer', 9,date '1965-06-09',  2450, NULL,  10);

1 row created.

SQL> insert into emp values(8,'Smart','SCJ', 'TRAINER', 4,date '1959-11-26',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(9,'Peter','CC',   'Designer',NULL,date '1952-11-17',  5000, NULL,  10);

1 row created.

SQL> insert into emp values(10,'Take','JJ', 'Tester',6,date '1968-09-28',  1500, 0,     30);

1 row created.

SQL> insert into emp values(11,'Ana','AA',  'TRAINER', 8,date '1966-12-30',  1100, NULL,  20);

1 row created.

SQL> insert into emp values(12,'Jane','R',   'Manager',   6,date '1969-12-03',  800 , NULL,  30);

1 row created.

SQL> insert into emp values(13,'Fake','MG',   'TRAINER', 4,date '1959-02-13',  3000, NULL,  20);

1 row created.

SQL> insert into emp values(14,'Mike','TJA','Manager',   7,date '1962-01-23',  1300, NULL,  10);

1 row created.

SQL>
SQL> select case mod(empno,2)
  2              when 0 then 'EVEN '
  3                     else 'ODD  '
  4         end  as empno
  5  ,      sum(sal)
  6  from   emp
  7  group  by mod(empno,2);

EMPNO   SUM(SAL)
----- ----------
EVEN       14025
ODD        14850

SQL>
SQL> drop table emp;

Table dropped.

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 case
15.	Group by course name then by begin date
16.	Group and count employeem and display only if its count is more than 4

Count all employees by even/odd employee id : GROUP BY « Select Query « Oracle PL / SQL

Related examples in the same category