CODEWITHSUNDEEP
sqlms-accesscalculated-columnsweighted-average
Chase
Chase

Reputation: 584

Weighted-Avg Access Query

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.

Upvotes: 0

Views: 1089

Answers (1)

Manuel Castro
Manuel Castro

Reputation: 138

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;

Upvotes: 1

Related Questions