In the below example i want to fetch the no of centers which has different subcenters. means for center 01 and 04 , subcenter are same. but for centers 05 and 07 , subcenters are different. so please help me writing a query that help me to fetch number of centers that have different subcenters.
---------------------- | Center | Subcenter | ---------------------- | 01 | 002 | | 01 | 002 | | 04 | 001 | | 04 | 001 | | 04 | 001 | | 05 | 001 | | 05 | 001 | | 05 | 002 | | 07 | 003 | | 07 | 003 | | 07 | 004 | | 07 | 005 | ----------------------
I want the result as below
-------------------- | count | center | -------------------- | 2 | 05 | | 3 | 07 | --------------------
because 05 has 001 and 002 (two different subcenter) , and 07 has 003, 004 and 005 (three different subcenter)
select center, count(distinct subcenter) as c
from x
group by center
having count(distinct subcenter) > 1
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments