CODEWITHSUNDEEP
sql-server
Masse Coder
Masse Coder

Reputation: 13

How to count two different column values in the same column?

I want to see the number of times a value has repeated before the next new value occurs.

This is what I am currently seeing:

---------------------------------------------------------------
| timestamp              | nameID | Value   | Row# | accountID|
---------------------------------------------------------------
| 2019-02-02 00:00:13:743| 17730  | Value 1 | 1    | 82607201 |
---------------------------------------------------------------
| 2019-02-02 00:00:14:743| 17730  | Value 2 | 1    | 82607201 |
---------------------------------------------------------------
| 2019-02-02 00:00:15:743| 17730  | Value 2 | 2    | 82607201 |
---------------------------------------------------------------
| 2019-02-02 00:00:16:743| 17730  | Value 2 | 3    | 82607201 |
---------------------------------------------------------------
| 2019-02-02 00:00:17:743| 17730  | Value 1 | 2    | 82607201 |
---------------------------------------------------------------
| 2019-02-02 00:00:18:743| 17730  | Value 2 | 4    | 82607201 |
---------------------------------------------------------------
| 2019-02-02 00:00:19:743| 17730  | Value 2 | 5    | 82607201 |
---------------------------------------------------------------
| 2019-02-02 00:00:20:743| 17730  | Value 2 | 6    | 82607201 |
---------------------------------------------------------------
| 2019-02-02 00:00:21:743| 17730  | Value 1 | 3    | 82607201 |
---------------------------------------------------------------
| 2019-02-02 00:00:22:743| 17730  | Value 2 | 7    | 82607201 |
---------------------------------------------------------------

This what I want to see:

---------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------

I've tried using Row_Number() OVER Partition, however it's not providing exactly what I am looking for.

SELECT [timestamp]
,nameID
,ROW_NUMBER() OVER(PARTITION BY Value ORDER BY timestamp ASC) AS Row#
,accountID
FROM #TEMP_Base3
ORDER BY timestamp ASC

Upvotes: 1

Views: 86

Answers (3)

MatthewTNguyen
MatthewTNguyen

Reputation: 11

--/** 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

Upvotes: 0

Zhorov
Zhorov

Reputation: 29943

You may try with next approach. First, find when the value is changed, then number groups and finally select data.

Input:

CREATE TABLE #Table (
   [timestamp] datetime,
   nameID int,
   [Value] varchar(10),
   accountID int
)
INSERT INTO #Table
   ([timestamp], nameID, Value, accountID)
VALUES
   ('2019-02-02 00:00:13:743', 17730, 'Value 1', 82607201),
   ('2019-02-02 00:00:14:743', 17730, 'Value 2', 82607201),
   ('2019-02-02 00:00:15:743', 17730, 'Value 2', 82607201),
   ('2019-02-02 00:00:16:743', 17730, 'Value 2', 82607201),
   ('2019-02-02 00:00:17:743', 17730, 'Value 1', 82607201),
   ('2019-02-02 00:00:18:743', 17730, 'Value 2', 82607201),
   ('2019-02-02 00:00:19:743', 17730, 'Value 2', 82607201),
   ('2019-02-02 00:00:20:743', 17730, 'Value 2', 82607201),
   ('2019-02-02 00:00:21:743', 17730, 'Value 1', 82607201),
   ('2019-02-02 00:00:22:743', 17730, 'Value 2', 82607201)

Statement:

;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

Output:

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

Upvotes: 2

JGFMK
JGFMK

Reputation: 8904

Use Group by

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

Upvotes: 0

Related Questions