CODEWITHSUNDEEP
sql-servervisual-studio-2010sql-server-2012
Sean
Sean

Reputation: 631

Removing Duplicate Values within Group By

I am using SQL-Server 2012 in Microsoft Visual Studio 2010.

As a preface, I cannot use ROW_NUMBER() OVER(PARTITION BY Col) as the OVER() method is not supported by the Visual Studio Version I am using. I am not in a place, unfortunately, where I can get new software.

I have a group of department and job IDs such that:

SELECT 
   Department, 
   Job_ID
FROM 
   Table1 
JOIN Table2 on 
Table1.id = Table2.id


Department       Job_ID
__________________________

 Marketing         J3
 Engineering       J1
 Marketing         J2
 Recruiting        J2
 Marketing         J8
 Administration    J3
 Recruiting        J1
 Administration    J5
 Administration    J1

I am trying to group by Job_ID, show the distinct Departments associated with each Job_ID, and only include groups that have more than 2 departments. The end results would be:

Department       Job_ID
__________________________

 Administration    J1
 Recruiting        J1
 Engineering       J1
 Marketing         J2
 Recruiting        J2
 Marketing         J3
 Administration    J3

I have tried:

SELECT Job_ID, count(distinct(Department)) as Dept_CountD
FROM Table1 JOIN Table2 on Table1.id=Table2.id
GROUP BY Job_ID
HAVING count(distinct(Department)) >1

This works, however, it does not group the departments along with each ID. I also know (and have tested) that I cannot insert Department into the Group By statement, as that would return distinct department counts only equal to 1!

I have tried building the above statement into a CTE and inner joining on the Job_ID so I only have Job_IDs that have more than 2 distinct departments associated with them, however, I end up with duplicate Departments in the grouping from the join.

I'm thinking perhaps joining to a sub query or doing a semi join?

Upvotes: 4

Views: 6949

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146499

try this: ( I am only guessing which table has the Departments and which has the Jobs cause you did not provide schema.

  SELECT Job_ID, count(*) Dept_CountD
  FROM Table1 a JOIN Table2 b on a.id=b.id
  where (Select count(*) from table1
         Where JobId = a.JobId) > 1

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

How about this:

SELECT 
   Department, 
   Job_ID
FROM Table1 
JOIN Table2 on Table1.id = Table2.id
WHERE Job_ID IN (SELECT Job_ID
                 FROM Table1 JOIN Table2 on Table1.id=Table2.id
                 GROUP BY Job_ID
                 HAVING count(distinct(Department)) >1)

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93724

Use COUNT() Over() window aggregate 

select * from 
(
select count(1)over(partition by Job_ID) as cnt,*
From <<join>>
) A
Where cnt > 1

Upvotes: 1

Related Questions