Can one use a where clause inside a partition clause?

Question

Can one use a where clause inside a partition clause?

ojmp2001 ojmp2001 121

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

MelissaMa-MSFT 24,221 Reputation points

2021-07-29T02:43:33.387+00:00

Hi @ojmp2001 ojmp2001 ,

Could you please validate all the answers so far and provide any update?

If all are not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa

Accepted answer

1 additional answer

Your answer

MelissaMa-MSFT 24,221 Reputation points

2021-07-29T02:43:33.387+00:00

Hi @ojmp2001 ojmp2001 ,

Could you please validate all the answers so far and provide any update?

If all are not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa

Answer 1

Hi @ojmp2001 ojmp2001 ,

Welcome to Microsoft Q&A!

You could also try with SUM as below:

SELECT DISTINCT Origin_ID, Origin_Name,  
Count(Status_ID) AS Total_status_count,  
SUM(CASE WHEN Status_Color = 'Red' THEN 1 ELSE 0 END) AS Red_count  
From #Temp  
GROUP BY Origin_ID, Origin_Name  
ORDER BY Origin_ID

OR

SELECT DISTINCT Origin_ID, Origin_Name,  
Count(Status_ID) Over (Partition by Origin_ID Order by Origin_ID) Total_status_count,  
SUM(CASE WHEN Status_Color = 'Red' THEN 1 ELSE 0 END)  Over (Partition by Origin_ID Order by Origin_ID) Red_count  
From #Temp  
ORDER BY Origin_ID

Output:

Origin_ID Origin_Name Total_status_count Red_count  
2 RCO 4 3  
3 USA 2 1  
4 CAN 1 0

By the way, the count of 'red' for 'CAN' could be 0 based on your sample data. Please help check again. Thanks.

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

The way to deal with this is with a CASE expression in the COUNT aggregate which only returns a non-NULL value for Red. Here is a query:

SELECT Origin_ID, Origin_Name,
         Count(Status_ID)AS Total_status_count,
         Count(CASE WHEN Status_Color = 'Red' THEN 1 END) AS Red_count
From #Temp
GROUP BY Origin_ID, Origin_Name

I also replaced DISTINCT + OVER with a GROUP BY clause, as this is the more normal way of writing a query like this.

Share via

Can one use a where clause inside a partition clause?

1 additional answer

Your answer