Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

awaria

IS-IT--Management
Sep 16, 2010
100
US
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
 
Use CTEs:

Code:
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
 
Try...

Code:
SELECT name, dept, COUNT(*) OVER (PARTITION BY NULL) count
  FROM YourTable
 
That is it. Like cte option. see many more uses for this function/expression
Thank you both for your posts and assistance!

awaria
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top