title: T SQL Count and Group by calculable column
body: <p>This code</p>
  <pre><code>DECLARE @SNH TABLE 
  (
      cntDATE date, 
      cntQUEUE varchar(10),
      [cntINKTONERBLACK] int
  )
  INSERT INTO @SNH (cntDATE, cntQUEUE, [cntINKTONERBLACK])
  VALUES ('2001-04-04', 'Queue01', 3),
         ('2001-04-05', 'Queue01', 1),
         ('2001-04-06', 'Queue01', 100)
  SELECT TOP 5
      [cntQUEUE] AS cntqueue,
      [cntdate],
      [cntINKTONERBLACK],
      (CASE 
           WHEN LAG(cntinktonerblack) OVER (PARTITION BY cntqueue ORDER BY cntqueue, cntdate) &lt; cntinktonerblack
               THEN 1 
               ELSE 0
       END) AS signalcolumn
  FROM 
      @SNH
  WHERE 
      [cntINKTONERBLACK] IS NOT NULL
  ORDER BY 
      cntqueue, cntDATE ASC
  </code></pre>
  <p>gives the following table</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>cntqueue</th>
  <th>cntdate</th>
  <th>cntINKTONERBLACK</th>
  <th>signalcolumn</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Queue01</td>
  <td>2001-04-04</td>
  <td>3</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Queue01</td>
  <td>2001-04-05</td>
  <td>1</td>
  <td>0</td>
  </tr>
  <tr>
  <td>Queue01</td>
  <td>2001-04-06</td>
  <td>100</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Is there a way to count '1' in signal column and group 1st column to have?</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>cntqueue</th>
  <th>NumberOfJumps</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Queue01</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>