Grouping question - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Grouping question

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-04-27 : 15:14:29

I have this query which returns the following sample data:


RC4	RC_DESC4	EMPLOYEE_ID	Product	UserID	Version	WorkstationID
B1A0	EB NE		T82284	Win XP Pro	t382284	SP3		PLYPGP68SD2D
B1A1	EB NE		T017261	Win XP Pro	t017261	SP3		ISGSAPB_2SSV392
B1A1	EB NE		T508214	Win XP Pro	t508214	SP3		SAPBDYFCXH2D
B1A1	EB NE		T508214	Win XP Pro	t508214	SP3		SAPB2J04X82D
B1A2	CNTRL		T542819	Win XP Pro	t542819	SP2		WISA013GLRNC2T

The query is here:


SELECT --V.Product
  E.RC4
, E.RC_DESC4
--, Count(E.RC4) CountOfRC4
, E.EMPLOYEE_ID
, V.Product
, V.UserID
, V.Version
, V.WorkstationID
FROM v_ValidOperatingSystemsInstallations V
LEFT JOIN ERS_ePeople E ON V.UserID = E.EMPLOYEE_ID
WHERE RC4 is not null
--GROUP BY RC4, RC_DESC4
ORDER BY RC4, RC_DESC4

Then, I group it here to show just RC4, RC_DESC4, and Count of RC4:


SELECT --V.Product
  E.RC4
, E.RC_DESC4
, Count(E.RC4) CountOfRC4
FROM v_ValidOperatingSystemsInstallations V
LEFT JOIN ERS_ePeople E ON V.UserID = E.EMPLOYEE_ID
WHERE RC4 is not null
GROUP BY RC4, RC_DESC4
ORDER BY RC4, RC_DESC4

Looking at the data line for that is:

RC4	RC_DESC4	CountOfRC4
B1A0	EB NE		1
B1A1	EB NE		3
B1A2	CNTRL		1

If you notice, the EmployeeID T508214 occurs on 2 rows. In my summary data, I only want to count it once. So there would be only 2 in the grouped data instead of 3 for that RC4 (B1A1).

How would I change the above group by query to count employees only once. We need a head count.

Thank you for any help.

Duane

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-27 : 15:32:12

[code]
SELECT RC4, RC_DESC4, COUNT(RC4) OVER(PARTITION BY RC4) AS CountOfRC4
FROM ....
ORDER BY RC4, RC_DESC4
[/code]

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 15:33:12

Instead of "Count(E.RC4)", try using "Count(DISTINCT E.EMPLOYEE_ID)"

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-27 : 15:37:02

Thanks, have to pay attention to the details.

also PM's count was wrong as well, B1A1 should be 2 according to his br.

quote:
Originally posted by DBA in the making

Instead of "Count(E.RC4)", try using "Count(DISTINCT E.EMPLOYEE_ID)"

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-04-27 : 15:43:00

Thank you both for your input DBA...s (Count Distinct) idea gave me only 1 Employee for each.
Thes same thing happened with hanbingl when I wrote it this way:
SELECT RC4, RC_DESC4, COUNT(RC4) OVER(PARTITION BY RC4) AS CountOfRC4
FROM v_ValidOperatingSystemsInstallations V
LEFT JOIN ERS_ePeople E ON V.UserID = E.EMPLOYEE_ID
WHERE RC4 is not null
GROUP BY RC4, RC_DESC4
ORDER BY RC4, RC_DESC4

Is that correct? When I left the Group By line out, I got the detail records, and when I left it in, I got 1 employee for each of 537 lines. The 537 lines is correct, but there should be multiple employees for most of the rows.

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-04-27 : 15:46:17

Oops: I read hanbingl's wrong. It did work. Thank you.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources