Weighted-Avg Access Query

Question

Background: I have the following table with clothing brand names, the count of the brand name sold, the revenue the brand name has brought in, and an avg per unit sold of that brand name.

Quantity Brand       Revenue       Rev_Per_Brand
1820     CMD         $13,519.50    $7.43
791      ATL         $8,997.00      $11.37
335      WHBM        $4,988.00      $14.89
320      CH          $4,593.50     $14.35
233      AT          $3,207.50     $13.77

Objective: Calculate the weighted average revenue by quantity sold of brands.

What I Have Now:

SELECT 
COUNT(*) AS [Quantity], 
Sales.Description AS Brand, 
FORMAT(SUM(Sales.Amt),"Currency") AS Revenue, 
Format(SUM(Sales.Amt)/COUNT(*), "Currency") AS Rev_Per_Brand, 

SUM(Sales.Amt)*(COUNT(*)/SUM(COUNT(*))) AS [wAvg_Rev_Per_Brand]

FROM Sales
WHERE Sales.Date > DateAdd("m",-1,NOW())
AND "This query lists brands that have sold in the past 1 month from today's date and returns the revenue received from them" <> ""
GROUP BY Sales.Description
ORDER BY COUNT(*) DESC;

Problem: I am receiving the cannot have aggregate function in expression error, and I am guessing it is probably in the SUM(COUNT(*)) part above.

I am simply trying to do the count of the specific brand over the total count (sum) of all brands sold. Can anyone tell me what I am doing wrong?

Thank you and I really appreciate any help in advance.

Sorry I deleted my answer since I misunderstood your question. May post later. Again what's your sales table? The table here seems already aggregated. — Tim3880, May 19 '15 at 05:09

score 1 · Accepted Answer · answered May 20 '15 at 16:49

You can not double aggregate i.e SUM(COUNT(*)) you've got to have that count in a separate subquery,

change your query to:

SELECT 
COUNT(*) AS [Quantity], 
Sales.Description AS Brand, 
FORMAT(SUM(Sales.Amt),"Currency") AS Revenue, 
Format(SUM(Sales.Amt)/COUNT(*), "Currency") AS Rev_Per_Brand, 

SUM(Sales.Amt)*(COUNT(*)/(SELECT Count(*) FROM sales)) AS [wAvg_Rev_Per_Brand]

FROM Sales
WHERE Sales.Date > DateAdd("m",-1,NOW())
AND "This query lists brands that have sold in the past 1 month from today's date and returns the revenue received from them" <> ""
GROUP BY Sales.Description
ORDER BY COUNT(*) DESC;

This worked perfectly, thank you very much! I guess I didn't know you could have a sub-SELECT statement in Access queries. Thanks, Manuel. — Chase, May 27 '15 at 06:24

Weighted-Avg Access Query

1 Answers1