title: How to create view with two counts having different where clause from same table
body: <p>I have two tables</p>
  
  <p><strong>Schools</strong>  </p>
  
  <pre><code>CREATE TABLE Schools 
  (
          BlockID INT,
          SchoolID VARCHAR, 
          SchoolName VARCHAR, 
          powerAvail BIT, 
          waterAvail BIT 
  )
  </code></pre>
  
  <p><strong>Blocks</strong></p>
  
  <pre><code>CREATE TABLE Blocks 
  (
      BlockID INT,
      BlockName VARCHAR 
  )
  </code></pre>
  
  <p>Inserting values:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>Note</strong>: the power available condition should not be dependent on water available.</p>