How to aggregate case expression within subquery?

I'm new to stack overflow and have been teaching myself SQL to build custom reports at work. I'd really appreciate if someone can point me in the right direction to return an average while using a case expression, here's the story:

I am trying to build a query that returns pricing data from sales invoices. I have a functional report, but I'm trying to standardize the pricing Unit of Measure as our costs are not always set to the same UOM as the sell price. I am limited in which code I can write as I am using an internal report writer that allows us to only write subqueries; I do not have direct access to the DBMS.

I have written a case statement in my subquery that solves the UOM challenge, but I'm stuck trying to aggregate the results.

After changing [View_QB_SalesOrderLine].[Price] to AVG([View_QB_SalesOrderLine].[Price], I tried aggregating my Subquery Case expression by using AVG both before and within the Case statement, but everything I try causes the query to fail.

The only code I have written below is the subquery, everything else is generated by the report writer tool. I greatly appreciate any help I can get!

Updated with query that caused error, rather that the working code I was trying to build upon...lesson learned. This query runs if I remove AVG from the CASE, but returns only [Price] averages grouped by MFR, CATALOG, and [SPA Cost Conv]. There is a new row for each instance of an item with a unique [SPA Cost Conv]. With AVG before the Case expression, the query errors(Error: Object reference not set to an instance of an object.) *This message seems generic as it is the only message I've ever seen displayed, no matter the problem with the query. Please keep in mind I can only write subqueries within this tool, all the other code is generated by the tool. I suspect the tool automatically adding the subquery to the Group By has something to do with the error when adding AVG to the subquery.

For context I added an image of what the GUI I'm working with looks like. I write the custom expressions in Notepad++ and paste them into the tool.Report Writer

SELECT  DISTINCT [Mfr] = [View_QB_SalesOrderLine].[Mfr]
,[CatalogNo] = [View_QB_SalesOrderLine].[CatalogNo]
,[SPA Cost Conv] = (AVG(CASE
    WHEN [Price UOM] = [SPA Cost UOM] 
    THEN [SPA Cost] 
    WHEN [Price UOM] = 'E' AND [SPA Cost UOM] = 'C'
    THEN [SPA Cost]/100
    WHEN [Price UOM] = 'E' AND [SPA Cost UOM] = 'M'
    THEN [SPA Cost]/1000
    WHEN [Price UOM] = 'C' AND [SPA Cost UOM] = 'E'
    THEN [SPA Cost] * 100
    WHEN [Price UOM] = 'C' AND [SPA Cost UOM] = 'M'
    THEN [SPA Cost] / 100
    WHEN [Price UOM] = 'M' AND [SPA Cost UOM] = 'E'
    THEN [SPA Cost] * 1000
    WHEN [Price UOM] = 'M' AND [SPA Cost UOM] = 'C'
    THEN [SPA Cost] * 100
END))
,[Price] = AVG([View_QB_SalesOrderLine].[Price])
,[Price UOM] = [View_QB_SalesOrderLine].[Price UOM]
FROM View_QB_SalesOrderLine
GROUP BY [View_QB_SalesOrderLine].[Mfr]
,[View_QB_SalesOrderLine].[CatalogNo]
,AVG(CASE
    WHEN [Price UOM] = [SPA Cost UOM] 
    THEN [SPA Cost] 
    WHEN [Price UOM] = 'E' AND [SPA Cost UOM] = 'C'
    THEN [SPA Cost]/100
    WHEN [Price UOM] = 'E' AND [SPA Cost UOM] = 'M'
    THEN [SPA Cost]/1000
    WHEN [Price UOM] = 'C' AND [SPA Cost UOM] = 'E'
    THEN [SPA Cost] * 100
    WHEN [Price UOM] = 'C' AND [SPA Cost UOM] = 'M'
    THEN [SPA Cost] / 100
    WHEN [Price UOM] = 'M' AND [SPA Cost UOM] = 'E'
    THEN [SPA Cost] * 1000
    WHEN [Price UOM] = 'M' AND [SPA Cost UOM] = 'C'
    THEN [SPA Cost] * 100
END)
,[View_QB_SalesOrderLine].[Price UOM]
ORDER BY [View_QB_SalesOrderLine].[Mfr]  ASC
,[View_QB_SalesOrderLine].[CatalogNo]  ASC

Solution

You've referred to subqueries and outer queries but nothing in the above appears to be either of those. This makes me wonder if you need to add a correlation condition. Also notice that the Price aggregate was not automatically added to the grouping columns. So I'm not sure I'm wondering if the extra parentheses or some unsupported syntax is causing that.

SELECT View_QB_SalesOrderLine.Mfr, View_QB_SalesOrderLine.CatalogNo,
    AVG("SPA Cost UOM" *
      CASE "Price UOM" + "SPA Cost UOM"
        WHEN 'EM' THEN 0.001
        WHEN 'EC' THEN 0.01
        WHEN 'CM' THEN 0.01
        WHEN 'CE' THEN 100
        WHEN 'MC' THEN 100
        WHEN 'ME' THEN 1000
        ELSE 1
      EN) AS "SPA Cost Conv",
    AVG(Price) AS Price
    /* , "Price UOM" -- this is not valid here */
FROM View_QB_SalesOrderLine
GROUP BY Mfr, CatalogNo
ORDER BY Mfr, CatalogNo

Long ago I worked with a query tool that would reorganize line breaks so when using comments it was safer to use the bracketed form /* */ rather than end-of-line --. You might want to strip out my commented line entirely.