CODEWITHSUNDEEP
sqlsql-servercount
kev670
kev670

Reputation: 880

Creating Muliple counts using different where clauses

The Below Statement works perfectly. It counts all the values from region 37000 and has a computed column no. What I want to do is add several more counts where I can change the where clause for instance to 38000 or 39000. Can anyone help me out... Thanks

SELECT a.region, COUNT(*) AS [computedCol1]    
(
SELECT  DISTINCT table1.serial1, table1.serial2,
    CASE WHEN table2.serial1 IS NULL THEN 'No' ELSE 'Yes' END AS [computedCol1],
    CASE WHEN table3.serial2 IS NULL THEN 'No' ELSE 'Yes' END AS [computedCol2],
    CASE WHEN table3.serial2 IS NULL AND table2.serial1 IS NULL THEN 'No' ELSE 'Yes' END AS [computedCol3]

FROM    table1
        LEFT JOIN table2
            ON table2.serial1 = table1.serial1
        LEFT JOIN dbo.EPG
            table3.serial2 = table1.serial2
)a where region = '37000' and [computedCol1]= 'No'
    group by a.region

Upvotes: 0

Views: 242

Answers (1)

mcha
mcha

Reputation: 2998

WHERE region in ('37000','38000','39000')

as you are grouping by region, you would get three different rows for each region with its count.

Upvotes: 3

Related Questions