Share via

Can one use a where clause inside a partition clause?

ojmp2001 ojmp2001 121 Reputation points
2021-07-27T20:19:57.603+00:00

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

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other

Answer accepted by question author

  1. MelissaMa-msft 24,246 Reputation points Moderator
    2021-07-28T01:29:14.74+00:00

    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.

    3 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator
    2021-07-27T21:01:53.65+00:00

    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.

    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.