The situation is like this:

I have a table that has one row for each copy of a letter, which also contains the letter ID. I want a single query that outputs each letter copy row with the letter copy ID, letter ID and count of all copies for the letter ID ...

Table Rows

Letter Copy ID      Letter ID
1                   1
2                   1
3                   1
4                   2
5                   2
6                   3

Expected Output

Letter Copy ID      Letter ID      Letter Copy Count
1                   1              3
2                   1              3
3                   1              3
4                   2              2
5                   2              2
6                   3              1

1 Answer 1


If you don't have access to window-functions, you can use a subquery:

SELECT Letter_Copy_ID
     , Letter_ID
     , ( SELECT COUNT(1) 
         FROM T AS T2
         WHERE T1.Letter_ID = T2.Letter_ID ) AS Letter_Copy_Count

If you have access to window functions:

SELECT Letter_Copy_ID
     , Letter_ID
     , COUNT(1) OVER (PARTITION BY Letter_ID) AS Letter_Copy_Count

You should avoid space in your identifiers (I used _ in my example), but if you feel you have to use them, you can quote them with ", as in "Letter Copy Count". Note that this makes the identifier case sensitive, so you will have to use the exact spelling.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.