question

ojmp2001ojmp2001-0652 avatar image
0 Votes"
ojmp2001ojmp2001-0652 asked MelissaMa-msft commented

Can one use a where clause inside a partition clause?

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




sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ojmp2001ojmp2001-0652 ,

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

1 Vote 1 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered MelissaMa-msft edited

Hi @ojmp2001ojmp2001-0652 ,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.