Count date field value, and calculation

  
SQL>
SQL>
SQL> CREATE TABLE emp (
  2    emp_id               NUMBER,
  3    ename             VARCHAR2(40),
  4    hire_date        DATE DEFAULT sysdate,
  5    end_date DATE,
  6    rate     NUMBER(5,2),
  7    CONSTRAINT emp_pk
  8      PRIMARY KEY (emp_id)
  9  );

Table created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, 'Mary', to_date('15-Nov-1961','dd-mon-yyyy'),null,169);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, 'Tom', to_date('16-Sep-1964','dd-mon-yyyy'),to_date('5-May-2004','dd-mon-yyyy'),135);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, 'Peter', to_date('29-Dec-1987','dd-mon-yyyy'),to_date('1-Apr-2004','dd-mon-yyyy'),99);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, 'Mike', to_date('15-Jun-2004','dd-mon-yyyy'),null,121);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, 'Less', to_date('2-Jan-2004','dd-mon-yyyy'),null,45);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, 'Park', to_date('1-Mar-1994','dd-mon-yyyy'),to_date('15-Nov-2004','dd-mon-yyyy'),220);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, 'Ink', to_date('4-Apr-2004','dd-mon-yyyy'),to_date('30-Sep-2004','dd-mon-yyyy'),84);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, 'Tike', to_date('23-Aug-1976','dd-mon-yyyy'),null,100);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, 'Inn', to_date('15-Nov-1961','dd-mon-yyyy'),to_date('4-Apr-2004','dd-mon-yyyy'),70);

1 row created.

SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, 'Kate', to_date('3-Mar-2004','dd-mon-yyyy'),to_date('31-Oct-2004','dd-mon-yyyy'),300);

1 row created.

SQL>
SQL>
SQL> SET ECHO ON
SQL> SELECT COUNT(SUBSTR(TO_CHAR(hire_date,'YYYY'),1,3) || '0') "decade",
  2         COUNT(hire_date) "hired",
  3         COUNT(hire_date) - COUNT(end_date) "remaining",
  4         MIN(hire_date) "first hire",
  5         MAX(hire_date) "last hire"
  6  FROM emp
  7  GROUP BY SUBSTR(TO_CHAR(hire_date,'YYYY'),1,3) || '0';
         3          3          1
15-NOV-61 16-SEP-64

         1          1          1
23-AUG-76 23-AUG-76

         1          1          0
29-DEC-87 29-DEC-87

         1          1          0
01-MAR-94 01-MAR-94

         4          4          2
02-JAN-04 15-JUN-04


5 rows selected.

SQL>
SQL>
SQL>
SQL> drop table emp;

Table dropped.

Related examples in the same category

1.	COUNT(column) and COUNT(*): count the number of rows passed into the function.
2.	Syntax: COUNT([DISTINCT]\|[ALL] )
3.	Using GROUP BY would tell us how many cars from each vendor we have on the lot:
4.	Find out which maker has a null value car with count()
5.	-Using the COUNT function in Having clause
6.	Example using the COUNT function with group by clause
7.	Count with group by: count for a group value
8.	Get the number of rows in each city group using the COUNT() function
9.	count null
10.	Column alias name for count function
11.	Count(*) - count(column_Name)
12.	count distinct column value
13.	count(1)
14.	count(distinct state)
15.	Count(*) and column renaming
16.	Count() with where clause
17.	Count department and calculate average salary
18.	Count employee in a department after grouping by department id
19.	Count employee, group by department id and job title
20.	count(*) - count(onhand)
21.	count(*) vs count(column name)

Count date field value, and calculation : COUNT « Aggregate Functions « Oracle PL / SQL

Related examples in the same category