1 Reply Latest reply on Apr 5, 2018 12:44 PM by ctlswadmin

    SQL Help!

    llemieux

      I was hoping someone may be able to give me a hand here in regards to what I'm doing wrong with my SQL query?

       

      Disclaimer, I am by no means a SQL expert and have pulled anything I do know from the interwebz.

       

      That being said, the below is a query I'm trying to use that will give me a report for the capacity levels on our storage arrays. What I would like to do is create a 6th column called "Percentage_Remaining", but am having trouble coming up with a method to get that number. I'm sure my thought process is off with this, but you can see where I'm going.

       

      P.S. I know there is a web copy of this report, but I will eventually need to find the TOTAL average % of space remaining of ALL arrays and the Web report is incapable of doing that.

       

      SELECT
      SRM_StorageArrays.Name AS Array_Name,
      SRM_StorageArrays.StorageArrayID AS Array_ID,
      SRM_StorageArrays.CapacityUserTotal AS Capacity_Total,
      SRM_StorageArrays.CapacityUserUsed AS Capacity_Used,
      SRM_StorageArrays.CapacityUserFree AS Capacity_Free,
      SRM_StorageArrays.CapacityUserFree / (SELECT SUM(SRM_StorageArrays.CapacityUserTotal) *100) FROM SRM_StorageArrays) AS Percentage_Remaining
      FROM
      SRM_StorageArrays
      GROUP BY SRM_StorageArrays.Name, SRM_StorageArrays.StorageArrayID, SRM_StorageArrays.CapacityUserTotal,
      SRM_StorageArrays.CapacityUserUsed, SRM_StorageArrays.CapacityUserFree