Link to home
Start Free TrialLog in
Avatar of GPSPOW
GPSPOWFlag for United States of America

asked on

Return # of records in a query as a field value for query

I am constructing a query that combines two tables and has 1010 rows.  I need to show the record count a field value within the query results.

Attached is output from the query with two fields EmployeeNumber and LicType (type of licenses the employee has).

I would like create a 3rd field showing the count of records (1010).

Here is my current code.
SELECT     dbo.tbl_License_Employee_Test.EmployeeNumber, COALESCE (dbo.tbl_LicensePro_License_Test.Type, '') AS LicType
FROM         dbo.tbl_License_Employee_Test LEFT OUTER JOIN
                      dbo.tbl_LicensePro_License_Test ON 
                      dbo.tbl_License_Employee_Test.EmployeeNumber = dbo.tbl_LicensePro_License_Test.EmployeeNumber
GROUP BY dbo.tbl_License_Employee_Test.EmployeeNumber, dbo.tbl_LicensePro_License_Test.Type

Open in new window


What is the syntax for the subquery to count the # of records in the output?

Thanks

Glen
LicType.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Anoo S Pillai
Anoo S Pillai
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GPSPOW

ASKER

Thank you