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

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:

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:

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

CodePudding user response：

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).

CodePudding user response：

Laszlo was correct, I had to rethink this and ended up using a WITH AS statement. This allowed me to group values by two categories and calculate the sum of those values. Then I selected the results of those and calculated the percentages.

WITH A AS (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 AS US LEFT OUTER JOIN
                                               dbo.AH_Property_Name_Link AS PL ON US.YD_Property = PL.YD_Property
                     GROUP BY US.DT_Uploaded, PL.AH_Property)
SELECT        DT_Uploaded, AH_Property, Vacant_Unrented_Ready, Vacant_Unrented_Not_Ready, FORMAT(CAST(Vacant_Unrented_Ready AS decimal) / NULLIF (CAST(Vacant_Unrented_Not_Ready AS int) 
                            CAST(Vacant_Unrented_Ready AS int), 0), 'P') AS Perc_Tot_Vac_Ready
 FROM            A AS B