Search code examples
sqlgroup-bysumpercentage

How to calculate percentage based off columns that summed from grouped values?

I have read several examples that are very close to what I'm trying to accomplish, but they don't have any joins or multiple columns that are grouped and summed. I'm trying to join to tables and group by date and property and sum the values of two additional columns.

I want to calculate a percentage from those two additional columns after the grouping and summation occurs.

Sample Data: enter image description here

Existing SQL Query - Obviously this fails to execute because I don't have US.Vacant_Unrented_Ready or US.Vacant_Rented_Ready in the Group By Clause.

SELECT US.DT_Uploaded, PL.AH_Property,  SUM(CAST(US.Vacant_Unrented_Ready AS int)) AS Vacant_Unrented_Ready, SUM(CAST(US.Vacant_Unrented_Not_Ready AS int)) AS Vacant_Unrented_Not_Ready, FORMAT(CAST(US.Vacant_Unrented_Ready AS decimal) 
/ NULLIF (CAST(US.Vacant_Unrented_Not_Ready AS int) + CAST(US.Vacant_Unrented_Ready AS int), 0), 'P') AS Perc_Tot_Vac_Ready

FROM dbo.AH_Unit_Availability_Summary US LEFT OUTER JOIN dbo.AH_Property_Name_Link PL ON US.YD_Property = PL.YD_Property

GROUP BY US.DT_Uploaded, PL.AH_Property

Desired End Result: enter image description here

What is the easiest way to accomplish that calculation in this query?

Solution 
  • SELECT DT_Uploaded, AH_Property, Vacant_Unrented_Ready, Vacant_Unrented_Not_Ready,
FORMAT(CAST(base.Vacant_Unrented_Ready AS decimal) 
/ NULLIF (CAST(base.Vacant_Unrented_Not_Ready AS int) + CAST(base.Vacant_Unrented_Ready AS int), 0), 'P') AS Perc_Tot_Vac_Ready
FROM (
  SELECT US.DT_Uploaded, PL.AH_Property,  
  SUM(CAST(US.Vacant_Unrented_Ready AS int)) AS Vacant_Unrented_Ready, 
  SUM(CAST(US.Vacant_Unrented_Not_Ready AS int)) AS Vacant_Unrented_Not_Ready, 
  FROM dbo.AH_Unit_Availability_Summary US 
  LEFT OUTER JOIN dbo.AH_Property_Name_Link PL ON US.YD_Property = PL.YD_Property
  GROUP BY US.DT_Uploaded, PL.AH_Property ) base

    I think you cannot refer on the same select statement sibling calculated column. You have to all columns write to group by, except summing/counting columns like sum(column_name), count(column_name), avg(column_name).