Get records count based on condition from another columns in SQL Server

mahesh213
 
on Sep 16, 2022 12:57 AM
431 Views

Hi,

How to get Count based upon value.

Id  ServiceNumber      Field CustOneValue    CustTwoValue

1    Sn01                      C1V     10.02              0.00

2    Sn02                      C2V      0.00               38.23

3    Sn01                      C1V      23.43             0.00

4    Sn01                      C1V      0.00               0.00

5    Sn02                      C2V      0.00               0.00

6    Sn02                      C2V      0.00               34.23

conditions:

1) if field value=C1V then need to check whether values exists in CustOneValue field or not based upon service number if yes make it as one count or else don't consider for count.

2) if field value=C2V then need to check whether values exists in CustTwoValue field or not based upon service number if yes make it as one count or else don't consider for count.

my expected o/p

NoOfValues exists 4

Can you please provide me sample sql query to produce my expected o/p

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 17, 2022 02:11 AM

Hi mahesh213,

Use CASE statement for count the value.

Refer below query.

SELECT SUM(
	CASE
		WHEN Field = 'C1V' AND CAST(CustOneValue AS DECIMAL) > 0 THEN 1
		WHEN Field = 'C2V' AND CAST(CustTwoValue AS DECIMAL) > 0 THEN 1
		ELSE 0
	END) AS [NoOfValues Exists]
FROM [Services]

Output

NoOfValues Exists

           4