How to create a column that shows the number of times a value in another column appears without combining rows in SQL Server

user_14140072 0
Viewed 55

I have a database that looks like this:

+----------------+
| Account Number | 
+----------------+
| A0001          |
| A0001          |
| A0001          |
| A0002          |
| A0003          |
| A0003          |
+----------------+

I need to create a column that has the number of times an Account Number appears without changing the number of rows.

I know that

SELECT Account Number, COUNT(*) AS Counts
FROM database
GROUP BY Account Number

returns

+----------------+--------+
| Account Number | Counts |
+----------------+--------+
| A0001          | 3      |
| A0002          | 1      |
| A0003          | 2      |
+----------------+--------+

But I need something that looks like this:

+----------------+--------+
| Account Number | Counts |
+----------------+--------+
| A0001          | 3      |
| A0001          | 3      |
| A0001          | 3      |
| A0002          | 1      |
| A0003          | 2      |
| A0003          | 2      |
+----------------+--------+

I am using Microsoft SQL Server.

tsql sql-server sql
1 Answers

you can use count as a window function here:

Select AccountNumber, 
 count(*) over (partition by AccountNumber) as counts
from Table
user_153326500
Related