Q:

Multiple join with a group by clause

I have three tables.
MainTable
HospitalId City  Data
1           1     20
1           1     10
2           2     50
3           3     100

Hospital
HospitalId, Staff
1            5
2            30
3            10

City
CityId City
1       Delhi
2       New York
3       Mumbai

I need to find the percentageform the city
ResultTable
City         Staff  Total Percentage
Delhi          10    30     
New York       30    50
Mumbai         10    100

I try doing the cound but It dosnt work with the group by clause.

A:

Your question is:

I need to find the percentage from the city

However, your results suggest a basic aggregation query:
select c.City, sum(h.Staff) as staff, sum(m.Data) as Total_Percentage
from City c join
     MainTable c
     on m.City = c.CityId join
     Hospital h
     on h.HospitalId = m.HospitalId
group by c.City;

I would not describe this query as returning the percentage from each city.  But I don't know what data means either.

