CODEWITHSUNDEEP
sqlsql-server-2008
user1056466
user1056466

Reputation: 667

Find Number of "-" in a table

I am trying to find number of "-" in a table,i am using this query but its not working

adapter.SelectCommand = new SqlCommand("select Count (*) from try
where DistrictName  = '-'  OR TownName  = '-'  
OR FarmerName = '-' OR Area = '-'"), con);*

Also How can i transform this query TO Find Number of "-" in each column?

Upvotes: 0

Views: 70

Answers (1)

Barmar
Barmar

Reputation: 781935

For your second question:

SELECT
    SUM(CASE WHEN DistrictName  = '-' THEN 1 ELSE 0 END) Districts,
    SUM(CASE WHEN TownName  = '-' THEN 1 ELSE 0 END) Towns,
    SUM(CASE WHEN FarmerName = '-' THEN 1 ELSE 0 END) Farmers,
    SUM(CASE WHEN Area = '-' THEN 1 ELSE 0 END) Areas
FROM try

For the first question, try this to get the total number of hyphens across all columns:

SELECT
    SUM((CASE WHEN DistrictName  = '-' THEN 1 ELSE 0 END) +
        (CASE WHEN TownName  = '-' THEN 1 ELSE 0 END) +
        (CASE WHEN FarmerName = '-' THEN 1 ELSE 0 END) +
        (CASE WHEN Area = '-' THEN 1 ELSE 0 END)) AS Hyphens
FROM try

DEMO

Upvotes: 4

Related Questions