How to add a column that shows the total number of rows in a table in SQL Server-CodePudding

I've got this table and I wish to add a column that gives the total number of rows in the table:

Table now:

Name         PAT_ID        
---------------------
Brian          123
Brian          356
Brian          3546
Brian          987

Desired output:

Name         PAT_ID       TOTAL     
------------------------------------
Brian          123          4
Brian          356          4
Brian          3546         4
Brian          987          4

Thank you!

CodePudding user response：

You can use OVER clause, like this:

CREATE TABLE PATIENT (
  Name VARCHAR(255) NOT NULL,
  PAT_ID INT NOT NULL
);

INSERT INTO PATIENT (Name, PAT_ID)
VALUES ('Brian', 123), ('Brian', 356), ('Brian', 3546), ('Brian', 987);

SELECT *
     ,COUNT(*) OVER () AS [total]
FROM PATIENT

In the OVER clause, you can use PARTITION BY which is like GROUP BY. For example, you can count the rows only for particular user:

SELECT *
     ,COUNT(*) OVER (PARTITION BY Name) AS [total]
FROM PATIENT