CODEWITHSUNDEEP
sqlaggregate
Turner
Turner

Reputation: 135

Count per category in SQL

I have what seems to be a simple issue, but of course it never is.

I have data that looks like this (I'm displaying pipe-delimited):

Identifier | Roll Up | Sequence

1234       | 4567    | 1

1234       | 4567    | 2

1235       | 4567    | 1

1235       | 4567    | 2

1236       | 6789    | 1

1237       | 6789    | 1

So, the Identifier is the 'version' of the Roll Up field, and each Roll Up field has sequences.

What I need to do is have an extra column that counts the 'version' of the Roll Up, as such:

Version |Identifier | Roll Up | Sequence

1       |1234       | 4567    | 1

1       |1234       | 4567    | 2

2       |1235       | 4567    | 1

2       |1235       | 4567    | 2

1       |1236       | 6789    | 1

2       |1237       | 6789    | 1

I thought it would be a simple count each Identifier per Roll Up but I couldn't get it to correctly do that with COUNT() or ROW_NUMBER().

Would anyone have a solution for that derived column?

Thanks much, Turner.

Upvotes: 1

Views: 175

Answers (1)

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

add a column to your simple query like this:

row_number() over (partition by Identifier, Roll_Up order by Identifier, Roll_Up)

I think would be the script producing the final result:

SELECT  t0.*, count1
FROM  Test AS t0 INNER JOIN
  (SELECT Identifier1, ROLLUP1,
     Row_Number() Over
   (Partition BY Identifier1, Rollup1
    ORDER BY Identifier1, RollUp1) AS count1
   FROM Test
   ) AS t1
  ON t0.Identifier1 = t1.Identifier1
  AND t0.ROLLUP1 = t1.ROLLUP1

Upvotes: 2

Related Questions