How to calculate average if SUM is being used

Question

I am trying to write a query that returns total percent average using all the percent rows.

I am having problems calculating the total average percent using the AVG function because the field passed uses another functions SUM that calculate the percent per row.

Example:

SELECT 
-- Row Percent Value
CONVERT(DECIMAL(5,4), 1.0 * SUM([col1]+[col2]+[col3]+[col4]+[col5]) / SUM([total])) AS 'Percent Row',
-- total rows average percent -- This part does not work
AVG(CONVERT(DECIMAL(5,4), 1.0 * SUM(CASE WHEN [col0] = 'Y' THEN [total] ELSE 0 END) / SUM([total])))

I am getting an error message:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I would appreciate any help on how to work this around, any insight is much appreciated.

UPDATE

This is my current query (without the average)

SELECT  
'Totals', 
CAST(CAST(SUM(CASE WHEN Gender = 'F' THEN Total ELSE 0 END) AS DECIMAL) / CAST(SUM(Total) AS DECIMAL) AS FLOAT) AS 'OA F' 
FROM RecordCount rc WITH(NOLOCK)
INNER JOIN Record r WITH(NOLOCK) 
    ON r.RecordId = rc.RecordId
WHERE 
    (rc.RecordId IN ('00001','00002'));

The Average needs to be included as another column, right after 'OA F', this is the query that gets the average and that I am unsure how to input in the existing query, correctly, mainly because it uses first the SUM function, and then the AVG:

SELECT AVG(o.[Avg%]) 
FROM 
(
    SELECT CAST(SUM(i.bnh + i.aa + i.[pi] + i.h + i.tmr)) AS DECIMAL)/ SUM(i.total) as [Avg%]
    FROM rc i
) AS o

Any help is greatly appreciate it.

Answer 1

Try this...

SELECT 'Percent Row', Avg(col2) 
FROM   (SELECT 
       -- Row Percent Value 
       CONVERT(DECIMAL(5, 4), 1.0 * Sum([col1] + [col2] + [col3] + [col4] + [col5]) / Sum([total]))  AS 'Percent Row', 
       -- total rows average percent -- This part does not work 
       CONVERT(DECIMAL(5, 4), 1.0 * Sum(CASE 
                                          WHEN [col0] = 'Y' THEN [total] 
                                          ELSE 0 
                                        END) / Sum([total])) AS Col2) SQ 

Or this...

;WITH cte 
     AS (SELECT 
        -- Row Percent Value  
        CONVERT(DECIMAL(5, 4), 1.0 * Sum([col1] + [col2] + [col3] + [col4] + [col5]) / Sum([total])) AS 'Percent Row', 
        -- total rows average percent -- This part does not work  
        CONVERT(DECIMAL(5, 4), 1.0 * Sum(CASE 
                                           WHEN [col0] = 'Y' THEN [total] 
                                           ELSE 0 
                                         END) / Sum([total])) AS Col2)

SELECT 'Percent Row', Avg(col2) 
FROM   cte 

Answer 2

Take it up a level:

select [percent row], avg(a) from(
SELECT 
CONVERT(DECIMAL(5,4), 1.0 * SUM([col1]+[col2]+[col3]+[col4]+[col5]) / SUM([total])) AS 'Percent Row', 
CONVERT(DECIMAL(5,4), 1.0 * SUM(CASE WHEN [col0] = 'Y' THEN [total] ELSE 0 END) / SUM([total])) a
from yourtable)b
group by [percent row]