Comment compter deux valeurs de colonne différentes dans la même colonne?

Utiliser le groupe par

SELECT column_name(s), count(1) as tally
FROM table_name
GROUP BY column_name(s)
ORDER BY column_name(s);

Vous pouvez essayer avec la prochaine approche. Tout d'abord, recherchez quand la valeur est modifiée, puis numérotez les groupes et enfin sélectionnez les données.

Entrée:

timestamp           nameID  Value   accountID   Count
02/02/2019 00:00:13 17730   Value 1 82607201    3
02/02/2019 00:00:14 17730   Value 2 82607201    1
02/02/2019 00:00:15 17730   Value 2 82607201    1
02/02/2019 00:00:16 17730   Value 2 82607201    1
02/02/2019 00:00:17 17730   Value 1 82607201    3
02/02/2019 00:00:18 17730   Value 2 82607201    1
02/02/2019 00:00:19 17730   Value 2 82607201    1
02/02/2019 00:00:20 17730   Value 2 82607201    1
02/02/2019 00:00:21 17730   Value 1 82607201    1
02/02/2019 00:00:22 17730   Value 2 82607201    

Déclaration:

;WITH ChangesCTE AS (
   SELECT 
      *,
      CASE
         WHEN [Value] = LAG([Value]) OVER (ORDER BY [timestamp]) THEN 0
         ELSE 1
      END AS ChangeMode
   FROM #Table
), GroupsCTE AS (
   SELECT 
      *,
      SUM(ChangeMode) OVER (ORDER BY [timestamp]) AS GroupID
   FROM ChangesCTE
)
SELECT 
   g.[timestamp],
   g.nameID,
   g.[Value],
   g.accountID,
   c.[Count]
FROM GroupsCTE g
LEFT JOIN (
   SELECT GroupID, COUNT(*) AS [Count] 
   FROM GroupsCTE 
   GROUP BY GroupID
) c ON g.GroupID = c.GroupId - 1

--/** DESIRED OUTCOME BASED ON STACK OVERFLOW **/ 
-----------------------------------------------------------------------------
--| timestamp              | nameID | Value   | Count of Value 2 | accountID|
-----------------------------------------------------------------------------
--| 2019-02-02 00:00:13:743| 17730  | Value 1 | (3)              | 82607201 |
----------------------------------------------------------------------------
--| 2019-02-02 00:00:17:743| 17730  | Value 1 | (3)              | 82607201 |
-----------------------------------------------------------------------------
--| 2019-02-02 00:00:22:743| 17730  | Value 1 | (1)              | 82607201 |
-----------------------------------------------------------------------------

    /** DATA PROVIDED **/ 
    IF OBJECT_ID('tempdb..#TEMP_data') IS NOT NULL
    DROP TABLE #TEMP_data 

    SELECT '2019-02-02 00:00:13:743' as timestamp,  17730 as nameID, 'Value 1' as Value, 1 as Row# , 82607201 as accountID INTO #TEMP_data 
    UNION SELECT '2019-02-02 00:00:14:743',  17730 , 'Value 2', 1  , 82607201 
    UNION SELECT '2019-02-02 00:00:15:743',  17730 , 'Value 2', 2  , 82607201 
    UNION SELECT '2019-02-02 00:00:16:743',  17730 , 'Value 2', 3  , 82607201 
    UNION SELECT '2019-02-02 00:00:17:743',  17730 , 'Value 1', 2  , 82607201 
    UNION SELECT '2019-02-02 00:00:18:743',  17730 , 'Value 2', 4  , 82607201 
    UNION SELECT '2019-02-02 00:00:19:743',  17730 , 'Value 2', 5  , 82607201 
    UNION SELECT '2019-02-02 00:00:20:743',  17730 , 'Value 2', 6  , 82607201 
    UNION SELECT '2019-02-02 00:00:21:743',  17730 , 'Value 1', 3  , 82607201 
    UNION SELECT '2019-02-02 00:00:22:743',  17730 , 'Value 2', 7  , 82607201 


    --------------------------------------------------


    SELECT MIN(timestamp)   as timestamp_value1 
        , nameID            as nameID           -- this query is assuming both name ID and account ID are tied to each other. 
        , 'Value 1'         as Value            -- this query is assuming there is only value 1 and value 2 
        , COUNT(*) - 1      as CountOfValue2    -- subtracting 1 because there is a VALUE 1 in each groupingID we've created.  
        , accountID 
    FROM ( 
                SELECT aa.timeStamp 
                    , aa.NameID 
                    , aa.Value 
                    , aa.Row# 
                    , aa.accountID 
                    , bb.groupid    
                FROM #TEMP_data aa 
                /** this section is getting me the date range groupings that happen between each Value 1. 
                This is assuming that row # is incrementing in order and resetting with each account id **/ 
                CROSS JOIN 
                ( 
                    SELECT aa.timeStamp                         as timeStart  
                        , ISNULL(bb.timeStamp, '3000-01-01')    as timeEnd  -- ISNULL 3000-01-01 because there is a situation where value 1 doesn't happen again in the last row 
                        , aa.nameID 
                        , aa.accountID 
                        , ROW_NUMBER() OVER (PARTITION BY aa.accountid order by aa.timestamp) as groupid 
                    FROM (SELECT * FROM #TEMP_data WHERE value = 'value 1') aa 
                    LEFT JOIN (SELECT * FROM #TEMP_data WHERE Value = 'Value 1') bb 
                        ON aa.row# + 1 = bb.row#
                        and aa.accountid = bb.accountid 
                ) bb 
                WHERE aa.accountid = bb.accountid 
                    and aa.timestamp >= bb.timeStart 
                    and aa.timestamp < bb.timeEnd 
            ) xx 
        GROUP BY xx.groupid 
            , xx.accountID 
            , xx.nameID