T SQL Count and Group by calculable column

This 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) < cntinktonerblack
             THEN 1 
             ELSE 0
     END) AS signalcolumn
FROM 
    @SNH
WHERE 
    [cntINKTONERBLACK] IS NOT NULL
ORDER BY 
    cntqueue, cntDATE ASC

gives the following table

cntqueue	cntdate	cntINKTONERBLACK	signalcolumn
Queue01	2001-04-04	3	0
Queue01	2001-04-05	1	0
Queue01	2001-04-06	100	1

Is there a way to count ‘1’ in signal column and group 1st column to have?

cntqueue	NumberOfJumps
Queue01	1

Answer

You can use a subquery:

select cntqueue, sum(signalcolumn)
from (select cntQUEUE, 
             (case when lag(cntinktonerblack) over (partition by cntqueue order by cntqueue, cntdate) < cntinktonerblack
                   then 1 else 0
              end) as signalcolumn
      from [watchdocstatsSU].[dbo].[queuescounters] 
      where [cntINKTONERBLACK] is NOT null
     ) q
group by cntqueue
order by cntqueue;