How to Perform Window Partitioning with Conditions (Oracle)

Question

I need to know how to narrow down my partitioning window with a condition. For instance, if I have the following:

SELECT
  T1.*,
  COUNT(T1.ID) OVER (PARTITION BY ID)
FROM TBL T1
WHERE /* some other conditions */;

I need the ID partition to only be a subset of all the identical ID's that have T1.TYPE = 'J'.

+---------+--------+---------------+--------+
| ID      | TYPE   | OTH1          | OTH2   |
+---------+--------+---------------+--------+
| 1       | K      | 500           | RER    |
| 1       | J      | 503           | LEL    |
| 1       | J      | 534           | KEL    |
| 2       | J      | 536           | NULL   |
| 2       | J      | 667           | ERT    |
| 2       | J      | 98            | NULL   |
+---------+--------+---------------+--------+

In this data set, I need to only consider my window count if TYPE = J, so for ID = 1, the count should be 2 rather than 3 since the first row is TYPE = K (ID = 2 where the count is 3).

Is this possible?

Answer 1

Perhaps conditional aggregation is what you want here:

SELECT
      t.*,
      COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID) cnt
FROM TBL t

If you only wanted to display this count for those records actually having the J type, then we could try:

SELECT
      t.*,
      CASE WHEN TYPE = 'J'
           THEN COUNT(CASE WHEN TYPE = 'J' THEN 1 END) OVER (PARTITION BY ID)
           ELSE 0 END cnt
FROM TBL t