Share via

Help with a Count Calculation

Lee_Walmsley 121 Reputation points
2022-04-19T10:34:25.213+00:00

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.

194210-image.png

Any help, id be much appreciated! Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
Accepted answer
  1. Jingyang Li 5,891 Reputation points
    2022-04-19T16:03:54.487+00:00

    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

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. LiHong-MSFT 10,046 Reputation points
    2022-04-20T02:32:37.397+00:00

    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.