Column in results set for each record needs to display the total record count for the query 1

[awaria]

Not sure how to run a query where a column will be populated with the value for the total number of records in the query results set, for each record in the record set.

If query results have 5 records, then the value 5 should be in column C for each of the five records.

name | dept | Col C

smith acct 5
jones mktg 5
wilson mmis 5
walker mmis 5
johnson admn 5

Appreciate your help.

Thanks, awaria

 

[TamarGranor]

Use CTEs:

WITH cteMainResult AS
(SELECT ... (whatever your main query is doing to grab name and dept)),

cteTotal (nTotal)
AS
(SELECT COUNT(*) FROM cteMainResult)

SELECT Name, Dept, nTotal
   FROM cteMainResult
     CROSS JOIN cteTotal

Tamar

 

[DaveInIowa]

Try...

SELECT name, dept, COUNT(*) OVER (PARTITION BY NULL) count
  FROM YourTable

 

[awaria]

That is it. Like cte option. see many more uses for this function/expression
Thank you both for your posts and assistance!

awaria