来自2个单独表的汇总计算 [英] Aggregate calculations from 2 separate tables

问题描述

我有一个SALES_RECEIPT表和一个(销售)RETURNS表，它们都具有REP_ID作为SALES_REP表中的外键.我想将每个销售代表的总销售额和总回报相加，并根据销售额计算佣金，并根据回报计算损失的佣金.

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.

查询运行没有错误，但是列中的值不正确.这些值比实际值要大得多.我尝试了INNER JOIN和LEFT JOIN来链接RETURNS表，但这并不能解决问题.

The query runs without an error but the values in the columns are not correct. The values are 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, 

 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*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], 
 Sum((Nz(SALES_RECEIPT.SELLING_PRICE,0)*Nz(SALES_RECEIPT.quantity,0))*(Nz(SALES_RECEIPT.commission_percent,100)*0.001))-Sum((Nz(RETURNS.SELLING_PRICE,0)*Nz(RETURNS.quantity,0))*(Nz(RETURNS.commission_percent,100)*0.001)) AS [Net 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;

推荐答案

为Access编辑(试图修复语法)

Edited for Access ( trying to fix syntax )

假设RETURNS表具有SALES_RECEIPT表的键ID?

Assuming that the RETURNS table has a Key ID to the SALES_RECEIPT table ?

首先，根据日期范围提取所有SalesReceipt记录 然后，根据相同的日期范围提取所有RETURNS

First, Pull all the SalesReceipt Records based on Date Range Then, Pull all RETURNS based on the same Date Range

WITH totSales as
(
    SELECT
        sr.SalesReceiptID
        ,sr.REP_ID
        ,Format(sr.SALE_DATE,'yyyy-mm') AS [Year-Month] 
        ,(sr.SELLING_PRICE * sr.quantity) AS [Total Sales] 
        ,((Nz(sr.SELLING_PRICE, 0) * Nz(sr.quantity, 0)) * (Nz(sr.commission_percent, 100) * 0.001)) AS [Gross Commission]
    FROM 
        SALES_RECEIPT sr
    WHERE 
        sr.SALE_DATE Between #1/1/2000# And #12/31/2050#
)

,totReturns as 
(
    SELECT
        r.REP_ID 
        ,(Nz(returns.selling_price * returns.quantity)) AS [Returns Sales] 
        ,((Nz(RETURNS.SELLING_PRICE, 0) * Nz(RETURNS.quantity, 0)) * (Nz(RETURNS.commission_percent, 100) * 0.001)) AS [Lost Commission]
    FROM
        RETURNS r
    WHERE
        r.RETURN_DATE Between #1/1/2000# And #12/31/2050#
)

Select
    ts.Year-Month
    ,sr.REP_Name
    ,SUM(ts.[Total Sales]) as 'TotSales'
    ,SUM(ts.[Gross Commission]) as 'TotCommissions'
    ,SUM(ISNULL(tr.[Return Sales],0) as 'TotReturnSales'
    ,SUM(ISNULL(tr.[Lost Commission],0) as 'TotLostCommissions'
From totSales ts
LEFT JOIN totReturns tr on tr.SalesReceiptID = ts.SalesReceiptID
INNER JOIN 
    SALES_REP sr ON ts.REP_ID = sr.REP_ID)
Group By ts.Year-Month, ts.REP_Name;

从您的最后一条评论中，我修改了第二CTE，以使所有RETURNS都与SALES_RECEIPT CTE中使用的日期范围相同.这样可以为您提供按特定销售代表分组的同一日期范围内的适当的销售，佣金，退货和损失的佣金总和.

From your last comment, I modified the 2nd CTE to get all RETURNS in the same date range used in the SALES_RECEIPT CTE. This should provide you with the proper sum of sales, commissions, returns, and lost commissions over the same date range grouped by specific Sales Rep.

这篇关于来自2个单独表的汇总计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！