How to create a column that shows the number of times a value in another column appears without comb-CodePudding

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.

CodePudding user response：

you can use count as a window function here:

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