How to obtain a count for each employee in results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to obtain a count for each employee in results

Author

Topic

Frozilla
Starting Member

3 Posts

Posted - 2012-09-17 : 16:53:26

I have a query that returns the following

Employee Name/EventStart/EventEnd etc.
Matt/jan/dec
Matt/feb/dec
Joe/feb/dec

and I'd like to get a count for each unique employee name, for example Matt-2 Joe-1

Is there any easy way to utilize the Count function to do a count of each unique employee name? I'm trying to build an internal report using SQL Report Builder 3.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 17:00:55

[code]
SELECT EmployeeName,COUNT(*) AS EmpCnt
FROM Table
GROUP BY EmployeeName
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-17 : 17:01:08

select EmployeeName, count(*) as EmployeeNameCount
from t
group by EmployeeName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-17 : 17:01:33

by 13 seconds!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Frozilla
Starting Member

3 Posts

Posted - 2012-09-17 : 17:06:49

Thanks for the response. I'm pretty new to all this SQL stuff so I'm learning as I go.

Do I need to "set" my query result as a new table for this to work? The result of the query is based on parameters the user puts in such as manager name and date range. Therefore the query would only display employees under that manager that had a trip in the designated date range.

Otherwise I tried doing some count stuff in SQL Server Management Studio and the count pops up easily, but in SQL Report Builder, I don't see anyway to pull in the count or where the count goes.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 17:14:24

first question is where do you want to show this result?

for adding Manager date filters you can extend query to


SELECT EmployeeName,COUNT(*) AS EmpCnt
FROM Table
WHERE ManagerName = @YourManager
AND DateField >= @startDate AND DateField < @EndDate +1
GROUP BY EmployeeName

you define parameters for managername,date ranges and pass value through them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Frozilla
Starting Member

3 Posts

Posted - 2012-09-17 : 17:22:41

I have all those parameters setup.

I have a report right now that has the user (manager) choose their name and then a date range. They run the report and get a list of all their employees that were out of office during that time range, what they were doing, where and from when to when. I also want this report to display each employees name during that given time period and a count of their trips.

I'm not sure if you're familiar with SQL Report Builder 3.0, but in SQL Server Management Studio if I insert a count function, the count displays on the screen. In SQL Report Builder 3.0, I get no syntax errors, but I don't see anyway to access the count and display it in my report.

Thank you for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 17:33:09

I know Report Builder
Report builder just builds a reporting model. So when you add count, it adds as a measure inside Report Builder

in order to use it somebody has to access report model using click once application from reporting server ,include the created report model and add the count measure you add for one of the reports they create

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources