count rows multiple columns

Question

I want to count rows based on multiple columns and with a specific value in a column. Please check the attached table.

Count the value of:

ID Date Action = 'C'

I tried

COUNT(*) OVER (PARTITION BY ID, Date, Action = 'C') AS Count, but it didn't work. Does one of you know how to calculate this field?

Thank you.

Answer 1

I think you want:

SELECT . . .,
       (CASE WHEN Action = 'C'
             THEN COUNT(*) OVER (PARTITION BY ID, Date, Action)
        END) as c_count

This puts the count in rows with Action = 'C'. If you want the count in all rows, then:

SELECT . . .,
       COUNT(CASE WHEN Action = 'C' THEN 1 ELSE 0 END) OVER (PARTITION BY ID, Date) as c_count