SQL查询以在给定条件下明确获取总和

Question

如何仅根据相似的列变量获取列的总和？请查看给出的示例以进一步了解问题。

这是样本数据:

Location    TENANT     Date         Sales     Area
AMALL       Tenant1    1/1/2016   1,000.00    50
AMALL       Tenant1    1/2/2016       0.00    50
AMALL       Tenant1    1/3/2016       0.00    50
So on..

AMALL       Tenant2    1/1/2016     500.00    60
AMALL       Tenant2    1/2/2016       0.00    60
AMALL       Tenant2    1/3/2016       0.00    60
So on..

AMALL       Tenant1    2/1/2016     800.00    50
AMALL       Tenant1    2/2/2016     200.00    50
AMALL       Tenant1    2/3/2016       0.00    50
So on..


AMALL       Tenant3    2/1/2016     600.00    50
AMALL       Tenant3    2/2/2016     600.00    50
AMALL       Tenant3    2/3/2016       0.00    50
So on..

如您所见，在一个给定位置，其中包含两个或多个不同的租户。每个人每天都有销售。此外，每个租户都有唯一的平方米或面积。我想要实现的是下面的示例输出。

预期输出

Location     Month   Total Sales    Total Area
Amall      January     1,500.00        110
Amall     February     2,200.00        100

请查看给定的预期输出。根据结果​​ 1,500 是 1 月份的总销售额，包括租户 1 和 2 的销售额(1000 + 500 = 1,500)，而总面积仅包括租户 1 和 2 的不同面积(50 + 60 = 110)

在 2 月份，只有租户 1 和 3 是贡献者，假设租户 2 已关闭并且根本没有销售。

基于总销售额，它是 2,200，来自 1000(租户 1)和 1200(租户 3)。这次的总面积应该是100(租户 1 的 50 + 租户 3 的 50)。租户 2 的 60 面积值(value)当然不包括在内，因为它在给定月份(2 月)没有销售

当前的错误输出

Location     Month   Total Sales    Total Area
Amall      January     1,500.00        110
Amall     February     2,200.00         50

根据应用的更新逻辑和代码，我能够更正一月份总面积的输出，在我的代码上使用 Distinct 命令，但是我在二月份不正确，只有 50 个，它应该是 100，因为租户 1 和租户 3 是不同的租户，应该总结他们各自的区域，因为我使用的是 Distinct 命令，系统将他们的区域视为一个，因此不总结他们的区域。

我怎样才能得到他们的总和，因为，Area 来自不同的或不同的租户

这是我的更新代码

SELECT location ,  tenant ,  a.date , sales, area
INTO #Temptable1
FROM  SalesTable



SELECT  tenants.location , months.number  ,months.MonthName 
    ,(sum(case when year(DATE) = 2016  then sales end)) as 'Total Sales'
    ,(sum(Distinct(case when year(DATE) = 2016  then area end))) as 'Total Area'

FROM 
    (       
    SELECT Number , DATENAME(MONTH, '2015-' + CAST(Number as varchar(2)) + '-1')
   'MonthName'
    FROM master..spt_values 
    WHERE Type = 'P' and Number between 1 and 12
    ) months

CROSS JOIN
    (       
    SELECT DISTINCT  locationd ,  location
    FROM #TempTable1
    ) tenants    

LEFT JOIN #TempTable1 t   
ON months.monthname = datename(month,t.date) and tenants.location = t.location 

GROUP BY  months.monthname  , tenants.location , months.number
ORDER BY  datepart(MM,months.monthname + '01 2000');

根据一位专家的建议，我刚刚在最后一行添加了一个 Distinct 命令，仍然没有给出答案。

希望您就主要问题分享您的专家建议。

Answer 1

这应该可以工作

为了能够测试和验证它，我创建了一个临时表

CREATE TABLE #Test 
(
Location    varchar(20),
Tenant      varchar(20),
[Date]      date,
Sales       decimal,
Area        int
)

INSERT INTO #TEST
SELECT 'AMALL', 'Tenant1', '1/1/2016', 1000.00, 50
UNION
SELECT 'AMALL', 'Tenant1', '1/2/2016', 0.00, 50
UNION
SELECT 'AMALL', 'Tenant1', '1/3/2016', 0.00, 50
UNION
SELECT 'AMALL', 'Tenant1', '1/4/2016', 0.00, 50
UNION
SELECT 'AMALL', 'Tenant1', '1/5/2016', 0.00, 50
UNION
SELECT 'AMALL', 'Tenant2', '1/1/2016', 500.00, 60
UNION
SELECT 'AMALL', 'Tenant2', '1/2/2016', 0.00, 60
UNION
SELECT 'AMALL', 'Tenant2', '1/3/2016', 0.00, 60
UNION
SELECT 'AMALL', 'Tenant2', '1/4/2016', 0.00, 60
UNION
SELECT 'AMALL', 'Tenant2', '1/5/2016', 0.00, 60

新的解决方案，在查看评论后

 ;WITH ATL(Area, Tenant, Location) AS
 (
    SELECT MAX(Area) Area, Tenant, Location 
    FROM #Test 
    GROUP BY Tenant, Location
 ),
 AreaLocation(Area, Location)
 AS
 (
    SELECT SUM(Area) Area, Location
    FROM ATL
    GROUP BY Location
 )
 SELECT
     T.Location,
     DATEPART(MONTH, T.[Date]) Month,
     SUM(T.Sales) [Total Sales],
    MAX(AL.Area) [Total Area]
 FROM
    #Test T
 JOIN
    AreaLocation AL ON T.Location = AL.Location
 GROUP BY
    T.Location, DATEPART(MONTH, [Date])

问题的旧解决方案:

SELECT
    Location,
    DATEPART(MONTH, [Date]) Month,
    SUM(Sales) [Total Sales],
    SUM(DISTINCT Area) [Total Area]
FROM
#Test
GROUP BY
Location, DATEPART(MONTH, [Date])

而且我还想删除表，以便在我进行更改时再次运行它

DROP TABLE #Test