CODEWITHSUNDEEP
sqloracle-database
Nicky
Nicky

Reputation: 1075

Write a Query to show Id, Name and No. of department?

I am trying to write a Query to show Id, Name and No. of department in given Table which are referring more than one department.

ID      Name     Department
--      ----     ----------
1       Sam      HR
1       Sam      FINANCE
2       Ron      PAYROLL
3       Kia      HR
3       Kia      IT

Result :

ID      Name     Department
--      ----     ----------
1       Sam               2
3       Kia               2

I tried using group by id and using count(*), but query is giving error.

How can I do this?

Upvotes: 0

Views: 131

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use window function with subquery :

select distinct id, name, Noofdepartment
from (select t.*, count(*) over (partition by id,name) Noofdepartment
      from table t
     ) t
where Noofdepartment > 1;

However, you can also use group by clause:

select id, name, count(*) as Noofdepartment
from table t
group by id, name
having count(*) > 1;

Upvotes: 1

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

You were right about using count(). You need to group by other columns though and only count unique departments then filter on the number in having clause.

select id, name, count(distinct department) as no_of_department
from table
group by id, name
having count(distinct department) > 1

This can also be done using analytic functions like below:

select *
from (
  select id, name, count(distinct department) over (partition by id, name) as no_of_department
  from table
) t
where no_of_department > 1

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143103

Without seeing your query, a blind guess is that you wrongly wrote the GROUP BY clause (if you used it) and forgot to include the HAVING clause.

Anyway, something like this might be what you're looking for:

SQL> with test (id, name, department) as
  2    (select 1, 'sam', 'hr'      from dual union
  3     select 1, 'sam', 'finance' from dual union
  4     select 2, 'ron', 'payroll' from dual union
  5     select 3, 'kia', 'hr'      from dual union
  6     select 3, 'kia', 'it'      from dual
  7    )
  8  select id, name, count(*)
  9  from test
 10  group by id, name
 11  having count(*) > 1
 12  order by id;

        ID NAM   COUNT(*)
---------- --- ----------
         1 sam          2
         3 kia          2

SQL>

Upvotes: 2

Related Questions