Counting/Summation within dataset

[Link82]

Hi everyone,
A few days ago I posted a question which someone did reply to. However, the tables have been re-arranged a bit and I didn't want to make my last thread confusing, so I thought I'd start fresh here. (Mod, you may close down my other thread; thanks).

So I have 2 tables.
Table 1 field details: managerID, managerUsername, managerLastName, managerFirstName

Table 2 field details: managerID, mappedEmployeeID

Table 1 lists all the managers with a few details. Table 2 contains data which maps employees to managers.

What I want is a list of managers (their IDs, last_name + ', ' + first_name as full_name) without repeats (manager X shouldn't appear more than once!) AND the NUMBER of employees that are mapped to a manager.

If I was getting normal data, the query would go something like this:

SELECT *
FROM    dbo.tblManagers a

INNER JOIN dbo.tblManagerAndEmployeeMappings b
ON              b.managerID = a.managerID

ORDER BY a.managerID

However, I want the sum of the number of employees that belong to each manager as well as the manager details. Hope I explained this properly. Feel free to ask me to clarify anything. I appreciate the help! (sknake thanks for replying in my other thread, hopefully you can help me here).

[sknake]

I replied to your old thread with a correct answer. Please go back and mark that thread as solved and I will continue to help:

http://www.daniweb.com/forums/thread202905.html

[Link82]

skanke, I did that. DIdn't realize there was that option. So yeah, we're pulling data from two tables here.

[sknake]

Should we account for duplicate entries in tblManagers or are you enforcing uniqueness?

This should work:

Select 
tblManagers.managerID,
MAX(tblManagers.managerUsername) As MgrUserName,
IsNull(tblManagers.managerLastName + ', ', '') + IsNull(tblManagers.managerFirstName, '') As FullName,
(
  Select Count(Distinct(tblManagerAndEmployeeMappings.mappedEmployeeID))
  From tblManagerAndEmployeeMappings (NOLOCK) --important to use nolock
  Where tblManagerAndEmployeeMappings.managerID = tblManagers.managerID
)
From tblManagers 
Group By tblManagers.managerID, tblManagers.managerLastName, tblManagers.managerFirstName
Order By tblManagers.managerID

[Link82]

Should we account for duplicate entries in tblManagers or are you enforcing uniqueness?

This should work:

Select 
tblManagers.managerID,
MAX(tblManagers.managerUsername) As MgrUserName,
IsNull(tblManagers.managerLastName + ', ', '') + IsNull(tblManagers.managerFirstName, '') As FullName,
(
  Select Count(Distinct(tblManagerAndEmployeeMappings.mappedEmployeeID))
  From tblManagerAndEmployeeMappings (NOLOCK) --important to use nolock
  Where tblManagerAndEmployeeMappings.managerID = tblManagers.managerID
)
From tblManagers 
Group By tblManagers.managerID, tblManagers.managerLastName, tblManagers.managerFirstName
Order By tblManagers.managerID

sknake, excellent! I see the results are correct and am trying to understand the query. Edit: I'm trying to return the Count as say, empCount, but am unsuccessful so far. Ideas? Got it, nvmd!

While I understand bits and pieces, I'm trying to tie it all together in my mind. While I can look up definitions online, sometimes it helps to have another human break it down for you. If you, or anyone else doesn't mind, please answer the following questions.

So the SELECT stmt within the parenthesis--I understand what you're doing there. You're getting a COUNT of the data wherever the managers match in both tables. I don't understand what NOLOCK means or does. This is the info I have dug up, and if incorrect or incomplete, please help me understand it:

"So, locks are used by databases to ensure data integrity. But, every once in a while, if two processes are trying to access the same data at around the same time, locks mean that queries might run a little slower than usual because one process will have to wait for the other to finish before it can run. That's where NOLOCK can come in handy. If you're running a SQL statement for, say, a report, and you know it's going to take a long time to run and be reading lots of records, you can use NOLOCK to speed it up."

Lastly I'm not sure how the GROUP By is working. I am kind of new to it, since I haven't had to use it up until now.

Any explanation is appreciated!! Thanks again, sknake!

[Link82]

p.s. sknake-- we are trying to enforce uniqueness, so hopefully that shouldn't be an issue!

[sknake]

So you have everything working? Please mark this thread as solved.

The (NOLOCK) prevents the table from being locked. Since you are doing a Count(*) on a broad set of data it might lock a lot of the table simply for counting records which will negatively affect other users trying to access the table.

The managerID should be unique and only have one record... that means you can group by every column in that table because it will be the same. You can either group by column, or max(column) in the select. I used both

[cgyrob]

Link82

Group by does exactly that; groups your result set by the fields provided, in the example provided by sknake. GROUP BY tblManagers.managerID, tblManagers.managerLastName, tblManagers.managerFirstName This will give you the number of rows selected grouped by Managers Id, Last Name, First Name so you get results like the following.

Id LastName First Name Count
1 Smith John 10
2 Jones Bill 8
3 Reilly Scott 15

Without the group by you would just get a list of all the records.

Id LastName First Name Count
1 Smith John 1
2 Jones Bill 1
2 Jones Bill 1
1 Smith John 1
3 Reilly Scott 1
3 Reilly Scott 1
...

I have not used [no lock], never thought about it before sknake's post. I would love an explanation of why it is important to use in this situation and others.

Learn a new thing everyday.

[sknake]

Actually -- You don't need the group by. I removed your join and did a subquery but didn't go back and remove the grouping.

You should delete the group by line entirely.

[Link82]

I'm trying to add another level to this query. This one works fine for the level 1 managers (the table I talked about initially, consider it to be table for level one managers).

There's a table for level 2 managers as well. Level 2 mappings work a little differently though. Level 2 managers are mapped to level 1 managers (who are in turn mapped to employees directly). So it's kind of a hierarchy. The idea is same: get level 2 manager information from tblLevel2Managers (same fields as level 1), but now.... get the mappings (level2 -> level 1 -> emps). When I say get the mappings, I mean get the # of employees that they are 'indirectly' mapped to.

Might sound confusing. I've started on this query, modelled after what sknake has showed me, but it's not right :(

p.s. sknake--If I take out group by, I get error message like "dbo.tblManager.managerID is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

[Link82]

p.s.s. Following is my attempt at getting level 2 info along with # of mapped emps, but it's not working and I know I'm not using it quite right (if the fields look slightly different, sorry, changing names slightly as I go):

SELECT		a.emplid, MAX(a.username) as mgrUsername, 
		IsNull(a.last_name + ', ', ' ') + 
                                IsNull (a.first_name, ' ') as name,
		(	
	                   SELECT	Count(DISTINCT(c.user_emplid))
	                   FROM dbo.tblLevelTwoManagerToLevelOneManager b	(NOLOCK)

		  INNER JOIN dbo.tblLevelOneManagerToEmployee c	
				ON			c.approver_emplid = b.level_two_emplid--user_emplid	

				WHERE		b.level_two_emplid = a.emplid
			)

FROM		dbo.tblLevelTwoManager a
GROUP BY	a.emplid, a.username, a. last_name, a.first_name
ORDER BY	name

[sknake]

Modified first query:

Select 
tblManagers.managerID,
tblManagers.managerUsername,
IsNull(tblManagers.managerLastName + ', ', '') + IsNull(tblManagers.managerFirstName, '') As FullName,
(
  Select Count(Distinct(tblManagerAndEmployeeMappings.mappedEmployeeID))
  From tblManagerAndEmployeeMappings (NOLOCK) --important to use nolock
  Where tblManagerAndEmployeeMappings.managerID = tblManagers.managerID
)
From tblManagers 
Order By tblManagers.managerID

The second query presents another situation. a level 2 manager might be associated to 5 level1 managers.. and a couple of the level1 managers might be mapped to the same employee. How do you want to handle that? This gives distinct employee count:

Select 
tblLevel2Managers.managerID,
tblLevel2Managers.managerUsername,
IsNull(tblLevel2Managers.managerLastName + ', ', '') + IsNull(tblLevel2Managers.managerFirstName, '') As FullName,
(
  Select Count(Distinct(tblManagerAndEmployeeMappings.mappedEmployeeID))
  From tblManagers (NOLOCK) Inner Join tblManagerAndEmployeeMappings (NOLOCK) ON (tblManagers.managerID = tblManagerAndEmployeeMappings.managerID)
  Where tblManagers.[PARENT MANAGER ID] = tblLevel2Managers.managerID
)
From tblLevel2Managers