CODEWITHSUNDEEP
sqlsql-servergroup-bycountmax
Jimmy White
Jimmy White

Reputation: 11

SQL Server Query: How do I get maximum counts for specific groupings?

We have a table with the following information:

Account ID, Touch Number, Type, Touch Date, and Stage (ranked 1-3, 1 if touch number < 50, 2 if 51-100, 3 if > 100).

Screenshot from table

I am looking to write a query that captures the type with the most touches at each stage for each account, looking something like this:

Output I am looking to receive

Here is the current query I wrote that is not working for me: 

`SELECT distinct
a.[Account ID],
a.Stage,
bb.Stage1TopType,
bb.TypeCount_1,
c.Stage2TopType,
c.TypeCount_2,
d.Stage3TopType,
d.TypeCount_3
FROM SFAX.dbo.LinearTest as a

--STAGE 1
LEFT JOIN 
(
SELECT 
a.[Account ID],
a.Type as Stage1TopType,
Max(b.TouchCount) as TypeCount_1
FROM SFAX.dbo.LinearTest as a

LEFT JOIN 
(
SELECT
[Account ID],
Type,
COUNT(TouchNumber) as TouchCount
FROM SFAX.dbo.LinearTest 
WHERE Stage = 1
GROUP BY [Account ID], Type
) as b on a.[Account ID] = b.[Account ID]

WHERE a.Stage = 1
GROUP BY a.[Account ID], a.Type

) as bb on a.[Account ID] = bb.[Account ID]


--STAGE 2
LEFT JOIN 
(
SELECT 
a.[Account ID],
a.Type as Stage2TopType,
Max(b.TouchCount) as TypeCount_2
FROM SFAX.dbo.LinearTest as a

LEFT JOIN 
(
SELECT
[Account ID],
Type,
COUNT(TouchNumber) as TouchCount
FROM SFAX.dbo.LinearTest 
WHERE Stage = 2
GROUP BY [Account ID], Type
) as b on a.[Account ID] = b.[Account ID]

WHERE a.Stage = 2
GROUP BY a.[Account ID], a.Type

) as c on a.[Account ID] = c.[Account ID]


--STAGE 3
LEFT JOIN 
(
SELECT 
a.[Account ID],
a.Type as Stage3TopType,
Max(b.TouchCount) as TypeCount_3
FROM SFAX.dbo.LinearTest as a

LEFT JOIN 
(
SELECT
[Account ID],
Type,
COUNT(TouchNumber) as TouchCount
FROM SFAX.dbo.LinearTest 
WHERE Stage = 3
GROUP BY [Account ID], Type
) as b on a.[Account ID] = b.[Account ID]

WHERE a.Stage = 3
GROUP BY a.[Account ID], a.Type

) as d on a.[Account ID] = d.[Account ID]

`

Please let me know if you have any suggestions on how I can receive my desired output.

Upvotes: 1

Views: 55

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35623

What I am looking for is the Type that appears the greatest number of times in each stage.

You will need to perform the count first, then decide which of these has the highest value. Below you will see the count performed in a "derived table", then row_number() is used to assign a value of 1 to the highest count, and finally we only return te rows with that value of 1

SELECT
    [Account ID]
  , Stage
  , TouchNumber
  , Type
  , TouchDate
  , type_count
FROM (
    SELECT
        [Account ID]
      , Stage
      , TouchNumber
      , Type
      , TouchDate
      , type_count
      , ROW_NUMBER() OVER (PARTITION BY [Account ID], Stage ORDER BY type_count DESC, Type) AS rn
    FROM (
        SELECT
            [Account ID]
          , Stage
          , TouchNumber
          , Type
          , TouchDate
          , COUNT( * ) OVER (PARTITION BY [Account ID], Stage, Type) AS type_count
        FROM YourTable AS T
    ) sq
) d
WHERE rn = 1
ORDER BY
    [Account ID]
  , Stage

nb. there might be more than one row with the same high value but only one row can be returned, if you want more then one row with for a tie use dense_rank() instead

Upvotes: 0

EzLo
EzLo

Reputation: 14209

I believe a simple ROW NUMBER window function should be enough.

;WITH MostTouchesByAccountStage AS
(
    SELECT
        T.[Account ID],
        T.Stage,
        T.TouchNumber,
        T.Type,
        T.TouchDate,
        Ranking = ROW_NUMBER() OVER ( -- Generate a ranking
            PARTITION BY
                T.[Account ID], -- That will reset with each different value of Account and Stage
                T.Stage
            ORDER BY
                T.TouchNumber DESC) -- And is ordered by TouchNumber descendently
    FROM
        YourTable AS T      
)
SELECT
    T.*
FROM
    MostTouchesByAccountStage AS T
WHERE
    T.Ranking = 1

Upvotes: 1

Related Questions