Help with a Count Calculation

Question

Hi all,

I am relatively new to writing SQL, and I was hoping to get some help with the following:

I have created a column already which used a CASE WHEN function to look at if one date is greater than another, and to return a "Y" or a "N". I now want to create another column which will be a COUNT of the above. So I want to do a count of all the "Y" per ID. So in the first example, id like all rows with an ID of 1, to return a 2 in this new column as there are two Y's for them.

Any help, id be much appreciated! Thanks

Answer 1

Please don't post an image for your question. Type them out.

You can try this:
SUM (CASE WHEN MET.IDENTIFIED_DTM>FAM.START_DTM) THEN 0 else 1 END) OVER (Partition by FAM.DIM_TF_FAMILY_ID) cnt

Answer 2

Hi @Lee_Walmsley
Considering that there are two columns in your result table that contain 'N'/'Y', the INITYALY_IDENTIFIED column is obtained by the CASE WHEN statement, and IS_TURNED_AROUND column exists in the FACT_TF_INDICATOR_MET table, so the calculation method for these two columns are somewhat different.

Below is a sample which you can refer to:

CREATE TABLE #TEST(ID INT,DATE_A DATE,DATE_B DATE,IS_TURNED_AROUND VARCHAR(10))  
INSERT INTO #TEST VALUES  
(1,'2022/4/20','2022/4/22','Y'),(1,'2022/3/20','2022/3/22','N'),  
(1,'2022/4/23','2022/4/22','Y'),(1,'2022/3/15','2022/3/16','N'),  
(1,'2022/4/25','2022/4/26','N'),(1,'2022/4/27','2022/4/28','N'),  
(2,'2022/4/20','2022/4/22','Y'),(2,'2022/3/20','2022/3/22','N'),  
(2,'2022/4/23','2022/4/22','Y'),(2,'2022/3/15','2022/3/16','Y'),  
(2,'2022/4/25','2022/4/24','N'),(2,'2022/4/27','2022/4/26','Y')  
  
--SELECT * FROM #TEST  
  
SELECT ID,  
       CASE WHEN DATE_A > DATE_B THEN 'N' ELSE'Y'END AS INITYALY_IDENTIFIED,  
	   SUM(CASE WHEN DATE_A > DATE_B THEN 0 ELSE 1 END)OVER(PARTITION BY ID) AS SUM_INITYALY_IDENTIFIED,  
       IS_TURNED_AROUND,  
	   SUM(CASE WHEN IS_TURNED_AROUND='Y' THEN 1 ELSE 0 END)OVER(PARTITION BY ID) AS SUM_IS_TURNED_AROUND  
FROM #TEST  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.