Category Archives: aggregate-functions

Aggregate calculations from 2 separate Tables

I have a SALES_RECEIPT table and a (sales) RETURNS table that both have REP_ID as a foreign key from the SALES_REP table. I want to sum total sales and total returns for each sales rep and calculate the commission from the sales and the lost commission from the returns. The query runs without an error and "Total Sales" and "Gross Commission" columns are correct. The "Return Sales" column is not accurate and has values much larger that what the actual values should be. I tried both INNER JOIN and LEFT JOIN to link the RETURNS table but that did not solve the problem.

SELECT Format(SALES_RECEIPT.SALE_DATE,'yyyy-mm') AS [Year-Month], 
SALES_REP.rep_Name AS [Sales Person], 

Sum(SALES_RECEIPT.SELLING_PRICE*SALES_RECEIPT.quantity) AS [Total Sales], 
Sum((Nz(SALES_RECEIPT.SELLING_PRICE,0)*Nz(SALES_RECEIPT.quantity,0))*(Nz(SALES_RECEIPT.commission_percent,100)*0.001)) AS [Gross Commission], 
Sum(Nz(returns.selling_price*returns.quantity)) AS [Returns Sales], 
Sum((Nz(RETURNS.SELLING_PRICE,0)*Nz(RETURNS.quantity,0))*(Nz(RETURNS.commission_percent,100)*0.001)) AS [Lost Commission]

FROM (SALES_RECEIPT
INNER JOIN SALES_REP ON SALES_RECEIPT.REP_ID = SALES_REP.REP_ID)
LEFT JOIN RETURNS ON SALES_RECEIPT.REP_ID = RETURNS.REP_ID
WHERE (((SALES_RECEIPT.SALE_DATE) Between #1/1/2000# And #12/31/2050#))
GROUP BY Format(SALES_RECEIPT.SALE_DATE,'yyyy-mm'), SALES_REP.rep_Name;