SQL - Count the occurence of one column value in another

Question

I have a table as shown below. Record key is unique per row

ID    RECORD_KEY CONCAT_REJECT    CONCAT_SUB
1     A34785      A123             23
1     B23845      R384             A123
1     H38959      Y345             A123

Expected Result

ID    CONCAT_REJECT    COUNT_REJECT_IN_SUB
1     A123             2
1     R384             0
1     Y345             0

How do I perform this count? I tried using COUNT(CONCAT_REJECT) over (PARTITION BY CONCAT_SUB). But it's not giving the desired result

Answer 1

Count concat_sub in the inner query and then do a left join with concat_reject to get final result. Here is the demo.

select
    id,
    concat_reject,
    coalesce(total, 0) as count_reject_in_sub
from myTable m
left join(
  select
      concat_sub,
      count(*) as total
  from myTable
  group by
      concat_sub
) m1
on m.concat_reject = m1.concat_sub

output:

| id  | concat_reject | count_reject_in_sub |
| --- | ------------- | ------------------- |
| 1   | A123          | 2                   |
| 1   | R384          | 0                   |
| 1   | Y345          | 0                   |