SQL Server中的Sum case

Question

下面的查询获取某一门店的总销售额。

输出：

SourceName       Amount
------------------------
Call Center      2997.00
Delivero Credit  5879.00
Eat Easily Cash    57.00

如果我有一个outlet列表，例如outlet 1和outlet 2，我想让so获得每个outlet的总销售额，如果没有sales sales amount，则应为零，因此outlet将为零。

SourceName      outlet 1   outlet 2
-----------------------------------
Call Center      2997.00    500
Delivero Credit  5879.00      0
Eat Easily Cash    57.00     66

下面的问题是query it double sum DeliveryCharge it it be SUM((OrderDetails.Quantity * OrderDetails.UnitPrice)) + Orders.DeliveryCharge - ((SUM((OrderDetails.Quantity * OrderDetails.UnitPrice))) * Orders.Discount / 100)

更新的查询：

SELECT
    s.SourceName,
    SUM(CASE
           WHEN o.OutletID ='1' THEN ((ord.Quantity * ord.UnitPrice) + ordd.DeliveryCharge)
           ELSE 0
        END) AS [Outlet 1],
    SUM(CASE
           WHEN o.OutletID ='1' THEN ((ord.Quantity * ord.UnitPrice) + ordd.DeliveryCharge)
           ELSE 0
        END) AS [Outlet 2]
FROM 
    dbo.Orders ordd
INNER JOIN 
    dbo.Outlets o ON ordd.OutletID = o.OutletID
INNER JOIN 
    dbo.OrderDetails ord ON ord.OrderID = ordd.OrderID
LEFT OUTER JOIN 
    dbo.Sources s ON s.SourceID = ordd.SourceID
WHERE  
    ordd.OrderID NOT IN (SELECT CanceledOrders.OrderID
                         FROM CanceledOrders)
    AND ordd.OrderDate BETWEEN '2017-05-1 10:00:00.000' AND '2017-06-1 04:00:00.000'
GROUP BY 
    s.SourceID, s.SourceName
ORDER BY
    s.SourceName

Answer 1

您当前正在按源名称分组两次，这对我来说毫无意义。除此之外，我认为您可以通过从GROUP BY中删除outlet名称并在该列上旋转来获得所需的输出。此外，我重构了您的查询，以便在每个订单的子查询中聚合订单详细信息。这应该可以避免重复的问题，您看到的是递送费用和可能的其他字段。

WITH MyGroupCte AS (
    SELECT
        s.SourceName,
        SUM(CASE WHEN out.OutletName = 'outlet 1'
                 THEN ((1 - (o.Discount / 100)) * od.OrderPrice) + o.DeliveryCharge
                 ELSE 0 END) AS [outlet 1],
        SUM(CASE WHEN out.OutletName = 'outlet 2'
                 THEN ((1 - (o.Discount / 100)) * od.OrderPrice) + o.DeliveryCharge
                 ELSE 0 END) AS [outlet 2]
    FROM dbo.Orders AS o
    INNER JOIN dbo.Outlets AS out
        ON o.OutletID = out.OutletID
    INNER JOIN
    (
        SELECT
            OrderID,
            SUM(od.Quantity * od.UnitPrice) AS orderPrice
        FROM dbo.OrderDetails
        GROUP BY OrderId
    ) od
        ON od.OrderID = o.OrderID
    INNER JOIN dbo.Sources AS s
        ON s.SourceID = o.SourceID
    WHERE
        out.OutletID = '6' AND
        o.OrderID NOT IN (SELECT OrderID FROM CanceledOrders) AND
        o.OrderDate
            BETWEEN '2017-05-1 10:00:00.000' AND '2017-06-1 04:00:00.000'
    GROUP BY
        o.OrderID,
        s.SourceName
)

SELECT
    SourceName,
    [outlet 1],
    [outlet 2]
FROM MyGroupCte;

请考虑使用别名，这将使您的查询更易于阅读和格式化。