Pl sql...find number of occurance

Suppose I am having a 2 st of records with date

table1 with 2 set of records

key1 startdate1  startdate2 startdate3
100  2349         2456       2345
100  3456         3878       2872

The date is in binary format. I need to get the count of date change that means, number of times the date has changed after one particular date ie, suppose after April 14th 2011, the number of times the date changes (for these two records with respect to particular key_1)

Clarification

Suppose I have three records:

+------+-------------+-------------+-------------+
| key1 | start_date1 | start_date2 | start_date3 |
+------+-------------+-------------+-------------+
| 701  | 08-SEP-2009 | 08-DEC-2009 | 08-jan-2010 |
| 701  | 08-JUN-2013 | 08-SEP-2013 | 08-DEC-2013 |
| 701  | 08-MAR-2017 | 08-MAR-2018 | 31-DEC-1899 |
+------+-------------+-------------+-------------+

I need to count the number of changed dates greater than 14-apr-2011. For example, for key 701, there are 5 changed dates greater than 14-Apr-2011. I need to get a table output with two fields:

+------+---------------+
| Key1 | changedcount  |
+------+---------------+
| 701  | 5        开发者_如何学编程     |
+------+---------------+

---

Try normalizing your table:

WITH
  normalized_data AS
  (
    SELECT key1, start_date1 AS start_date FROM MYTABLE
  UNION ALL
    SELECT key1, start_date2 AS start_date FROM MYTABLE
  UNION ALL
    SELECT key1, start_date3 AS start_date FROM MYTABLE
  )

SELECT
  key1,
  COUNT(DISTINCT start_date) AS changedcount
FROM
  normalized_data
WHERE
  start_date > your_start_date_here
GROUP BY
  key1

---

You may try this:

select sum(d1)+sum(d2)+sum(d3) , key
 from
(
select
  t.key,
  (select count(*) from test t1 where t1.key = t.key and start_date1>to_date('14-Apr-2011','DD-mon-yyyy')) d1,
  (select count(*) from test t2 where t2.key = t.key  and start_date2>to_date('14-Apr-2011','DD-mon-yyyy')) d2,
  (select count(*) from test t3 where t3.key = t.key  and start_date3>to_date('14-Apr-2011','DD-mon-yyyy')) d3
from  test t
group by key
)
group by key

You could also try ORACLE PIVOT() function if you are using 11g or above. I dont have 11g installed at the moment but if you need an example i can give you one with this function.

Regards, Alex

---

Now that you given more clear question:

select key1, count(distinct start_date)
from (
    select key1, start_date1 as start_date from myTable
    UNION ALL
    select key1, start_date2 as start_date from myTable
    UNION ALL
    select key1, start_date3 as start_date from myTable
) normalFormTable N
where start_date > myGivenDate
group by key