Calculate Count of records if Value is not null in SQL Server

mahesh213
 
on Jul 18, 2022 11:45 PM
323 Views

HI,

I have one table with fields

Id - int

Value- varchar

Name - varchar

Flag- char

Id   Name   Value Flag

1     a         19      Y

2      b         30     N

3      c         50      Y

4      d                   Y

5       e                 N

my requirement is that need to calculate count value if value not equal to null.

this was expected o/p

Id   Count     Flag

1         2        Y

2          1       N

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 18, 2022 11:48 PM

Hi mahesh213,

Check the Value column with IS NOT NULL.

Refer below query.

SQL

DECLARE @Test AS TABLE(Id INT,[Name] VARCHAR(10),[Value] INT,Flag CHAR(1))
INSERT INTO @Test VALUES(1,'a',19,'Y')
INSERT INTO @Test VALUES(2,'b',30,'N')
INSERT INTO @Test VALUES(3,'c',50,'Y')
INSERT INTO @Test VALUES(4,'d',NULL,'Y')
INSERT INTO @Test VALUES(5,'e',NULL,'N')
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) Id, 
	COUNT(*) 'Count',Flag 
FROM @Test
WHERE [Value] IS NOT NULL
GROUP BY Flag
ORDER BY Flag DESC

Output

Id Count Flag
1 2 Y
2 1 N