Is there a way to add a where clause when doing a partition?
I have several columns and want to add 2 more column whereby I will only count total status for each origin and Total red status where status color = red only.
Create table #Temp
(
Origin_ID INT
,Origin_Name VARCHAR(3)
,Status_ID INT
,Status_Color VARCHAR(10)
)
Go
Insert into #Temp Values (2, ‘RCO’, 1, ‘Red’)
Insert into #Temp Values (2, ‘RCO’, 2, ‘Red’)
Insert into #Temp Values (2, ‘RCO’, 3, ‘Green’)
Insert into #Temp Values (2, ‘RCO’, 3, ‘Red’)
Insert into #Temp Values (3, ‘USA, 1, ‘Red’)
Insert into #Temp Values (3, ‘USA’, 2, ‘Green’)
Insert into #Temp Values (4, ‘CAN’, 1, ‘Green’)
SELECT DISTINCT Origin_ID, Origin_Name
,Count(Status_ID) Over (Partition by Origin_ID Order by Origin_ID) Total_status_count
--how do i calculate the Total Red_status_count only
From #Temp
Origin_ID Origin_name Total_Status_count Total_red_status
2 RCO 4 3
3 USA 2 1
4 CAN 1