asked
Find the percentage with multiple Join.
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 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. data column is the sales.I need to find the sales Percentage related to each city. I have tried this but I am not getting the desired result. SELECT C.City ,COUNT(M.City)*MAX(H.Staff) Staff ,SUM(M.Data)Total_Percentage FROM City C JOIN MainTable M ON M.City=C.CityId JOIN Hospital H ON H.HospitalId=M.HospitalId GROUP BY C.City
Your query does give you the result that you wanted - why do you think it does not work?
Do you mean something like this? DECLARE @MainTable TABLE (HospitalID INT, CityID INT, Data INT); DECLARE @Hospital TABLE (HospitalID INT, Staff INT); DECLARE @City TABLE (CityID INT, City VARCHAR(50)); INSERT INTO @MainTable VALUES (1,1,20),(1,1,10),(2,2,50),(3,3,100); INSERT INTO @Hospital VALUES (1,5),(2,30),(3,10); INSERT INTO @City VALUES (1,'Delhi'),(2,'New York'),(3,'Mumbai'); SELECT C.City, COUNT(M.CityId) * MAX(H.Staff) * 100 / SUM(COUNT(M.CityId) * MAX(H.Staff) ) OVER (PARTITION BY 1) FROM @City AS C JOIN @MainTable AS M ON M.CityID = C.CityID JOIN @Hospital AS H ON H.HospitalID = M.HospitalID GROUP BY C.City;
17 People are following this question.
