SSMS Count with HAVING - SQL

Transact-SQL
1

Hi

I'm trying to count the number of Departments in a database where there are exaclty 6 employees in each Department.

I've written the query below, but it returns the rows with the Departments where there are 6 employees.

But what I'd like is the TOTAL NUMBER of ROWS where each Department has 6 employees.

Does anyone know how I can modify this query to give me a total number, please? I'm using Sequel Server Management Studio 2012.

TIA

select count(Department)
--Department as [Department Name]
from HumanResources.vEmployeeDepartment
GROUP BY Department
HAVING count(Department) = 6

2

Without any schema definition and sample data, it will be just a guess

select count(1)
 from vEmployeeDepartment d
where exists(
select 1
 from vEmployeeDepartment tgt
where d.departmentId = tgt.departmentId 
group by EmployeeId
HAVING count(1) = 6
)
3

hi

hope this helps :slight_smile:

Please click arrow to the left for

Sample Data Script 
drop table #SampleData
go 

create table #SampleData
(
dept_id int 
,employee_id int 
) 
go 

insert into #SampleData select 1,1
insert into #SampleData select 1,2
insert into #SampleData select 1,3
insert into #SampleData select 1,4
insert into #SampleData select 1,5
insert into #SampleData select 1,6


insert into #SampleData select 2,1
insert into #SampleData select 2,2
insert into #SampleData select 2,3
insert into #SampleData select 2,4
insert into #SampleData select 2,5
insert into #SampleData select 2,6
insert into #SampleData select 2,7
insert into #SampleData select 2,8


insert into #SampleData select 3,1
insert into #SampleData select 3,2
insert into #SampleData select 3,3
insert into #SampleData select 3,4
go 


select 'Sample Data',* from #SampleData 
go

SELECT
    'SQL OutPut'
    , [#SampleData].[dept_id]
    , COUNT(DISTINCT [#SampleData].[employee_id])
FROM
    [#SampleData]
GROUP BY
    [#SampleData].[dept_id]
HAVING
    COUNT(DISTINCT [#SampleData].[employee_id]) = 6;

image

1 Like
4

hi

here is the Total_Number_of_Rows ... having 6

; with cte as 
(
SELECT
     [#SampleData].[dept_id]
    , COUNT(DISTINCT [#SampleData].[employee_id]) as cnt_dis
FROM
    [#SampleData]
GROUP BY
    [#SampleData].[dept_id]
HAVING
    COUNT(DISTINCT [#SampleData].[employee_id]) = 6
	) 
	select a.* from #SampleData a join cte b on a.dept_id = b.dept_id

image

1 Like
5

Sounds like your sql is a little wonky and the version of Management Studio doesn't matter as much as the version of SQL Server.

select Department, count(1)
--Department as [Department Name]
from HumanResources.vEmployeeDepartment
GROUP BY Department
HAVING count(1) = 6
6

I think this will work, but I don't have any way to test it since you didn't post any usable test data.

SELECT count(distinct Department)
--Department as [Department Name]
FROM HumanResources.vEmployeeDepartment
GROUP BY Department
HAVING count(Department) = 6

7

Thanks @harishgg1

8

This code didn't actually solve the problem.

It just gave a list of rows with Departments with 6 people in them - which is the same thing that my code did.

But I wanted to return ONE row which returned the TOTAL number of Departments with 6 people in them.

9

Hi Scott

Thanks for your response.

I tested the code out, but it gave a list of rows with the Departments which had 6 employees in them.

However, I wanted to return one row which had the total number of Departments with 6 employees in them.

I managed to get an answer, though.

Thanks for posting your solution, however.

10

Hi NewGuy

What you were wanting
Is very very very easy to do

We just did not know what you were looking for

Always helps to show and explain with
Data
:+1::+1:

1 Like
11

You need to combine the 2 sql statements provided

select count(Distinct Department) from (
select Department, count(1) N
--Department as [Department Name]
from HumanResources.vEmployeeDepartment
GROUP BY Department
HAVING count(1) = 6) x
2 Likes
12

Awesome!

This is the most helpful answer, as it uses the code that I'd originally written (with your modifications, of course).

And it actually works!

Thanks @mike01!

13

That's actually almost identical to the final code I posted after I made changes to my original post. But as long as you got a solution.

1 Like
14

Scott, your code produced multiple records. OP wanted a count of departments that had employees.

create table #SampleData
(
dept_id int 
,employee_id int 
) 
go 

insert into #SampleData select 1,1
insert into #SampleData select 1,2
insert into #SampleData select 1,3
insert into #SampleData select 1,4
insert into #SampleData select 1,5
insert into #SampleData select 1,6


insert into #SampleData select 2,1
insert into #SampleData select 2,2
insert into #SampleData select 2,3
insert into #SampleData select 2,4
insert into #SampleData select 2,5
insert into #SampleData select 2,6
insert into #SampleData select 2,7
insert into #SampleData select 2,8


insert into #SampleData select 3,1
insert into #SampleData select 3,2
insert into #SampleData select 3,3
insert into #SampleData select 3,4
insert into #SampleData select 3,5
insert into #SampleData select 3,6

SELECT count(distinct dept_id)
--Department as [Department Name]
FROM #SampleData
GROUP BY dept_id
HAVING count(dept_id) = 6

select count(Distinct dept_id) from (
select dept_id, count(1) N
--Department as [Department Name]
from #SampleData
GROUP BY dept_id
HAVING count(1) = 6) x
15

That's why I said "almost identical" and not "identical" :-).