# Help with a Count Calculation

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.

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,126 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions

1. 5,891 Reputation points
2022-04-19T16:03:54.487+00:00

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. 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