CODEWITHSUNDEEP
sqlsql-server
Manik Arora
Manik Arora

Reputation: 175

How to create view with two counts having different where clause from same table

I have two tables

Schools 

CREATE TABLE Schools 
(
        BlockID INT,
        SchoolID VARCHAR, 
        SchoolName VARCHAR, 
        powerAvail BIT, 
        waterAvail BIT 
)

Blocks

CREATE TABLE Blocks 
(
    BlockID INT,
    BlockName VARCHAR 
)

Inserting values:

Insert into Schools (1, 1, 'a', 0, 1)
Insert into Schools (2, 2, 'b', 1, 1)
Insert into Schools (2, 3, 'c', 0, 1)

Insert into Blocks (1, 'A')
Insert into Blocks (2, 'B')

Now I have to create a view to list 3 columns: Block Name, Number of schools in a block with power available 1, Number of schools in a block with water available 1 grouping result by block name.

Note: the power available condition should not be dependent on water available.

Upvotes: 0

Views: 91

Answers (2)

jose_bacoy
jose_bacoy

Reputation: 12684

You can join tables School and Blocks on BlockID then do a sum when power or water available is 1.

SELECT 
    B.BlockName,
    SUM(case when powerAvail=1 then 1 else 0 end) as 'Count_Schools_Power_Available',
    SUM(case when waterAvail=1 then 1 else 0 end) as 'Count_Schools_Water_Available'
FROM
    Schools S,
    Blocks  B
WHERE
    S.BlockID = B.BlockID
GROUP BY
    B.BlockName;

Result:

BlockName   Count_Schools_Power_Available   Count_Schools_Water_Available
   A        0                               1
   B        1                               2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You should be able to do:

SELECT B.BlockName,
    SUM(CAST(powerAvail as int)) as num_powerAvail,
    SUM(CAST(waterAvail as int)) as num_waterAvail
FROM Schools S JOIN
     Blocks  B
     ON S.BlockID = B.BlockID
GROUP BY B.BlockName;

Upvotes: 1

Related Questions