SELECT with a GROUP BY column?

Question

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

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
FROM T AS T1

If you have access to window functions:

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

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.