CODEWITHSUNDEEP
sqlsql-serverjoin
Fhd.ashraf
Fhd.ashraf

Reputation: 537

Trying to count particular column from three different tables

Master table

SerNo   HospitalId  CityId
1       1           1
2       1           1
3       2           2
4       3           2
5       1           1

HospitalMaster

HospitalId  HospitalName
1           ABC
2           XYZ

CityMaster 

CityId      City
1           Delhi
2           Bombay

Result I need something like this

City    TotalHospital
Delhi   1
Bombay  2

I tried joining the tables but I keep getting the total rows of the columns and not of the hospitals. Thank you.

Upvotes: 1

Views: 75

Answers (5)

JyothiJ
JyothiJ

Reputation: 315

As we need city name along with count, we can get by join city master and master tables.

    select max(C.cityname), count(distinct M.HospitalId) 
      from CityMaster C 
inner join Master M 
        on C.Cityid = M.CityId
  group by M.cityid

Upvotes: 0

Wocugon
Wocugon

Reputation: 586

You can do it with using JOIN Just replace #city, #hospital, #table with your table names.

select C.City,T.CityId from #city C,#hosp H,#table T WHERE T.CityId = C.CityId AND T.HospitalId = H.HospitalId Group by C.City,T.CityId

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

Left join the city master table to a subquery which finds the hospital counts for each city. Note carefully that we only count distinct hospitals, because a hospital city relationship may appear more than once in the master table.

SELECT t1.City, COALESCE(t2.cnt, 0) AS TotalHospital
FROM CityMaster t1
LEFT JOIN
(
    SELECT CityId, COUNT(DISTINCT HospitalId) cnt
    FROM Master
    GROUP BY CityID
) t2
    ON t1.CityId = t2.CityId;

enter image description here

Demo

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

Try this:

SELECT C.City,COUNT(DISTINCT HospitalID)TotalHospital 
FROM CityMaster C
JOIN Master_table M ON M.CityId=C.CityId
GROUP BY C.City

Upvotes: 0

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

You could apply join

select M.City,count(distinct M.HospitalId) from CityMaster C inner join Master M ON C.CityId = M.CityId
group by M.City

Upvotes: 0

Related Questions