SQL SELECT multiple with sum

SQL SELECT multiple with sum

提问by henryaaron

I'm trying to do a select SUM while doing other selects at the same. My current script:

我正在尝试在进行其他选择的同时进行选择 SUM。我目前的脚本：

SELECT Orders.OrderID,SUM(Trackingnumbers.Shipment_Cost) AS Shipping_Cost
FROM Orders 
INNER JOIN Trackingnumbers
ON Orders.OrderID = TrackingNumbers.OrderID
WHERE Orders.OrderStatus = 'Shipped' AND Orders.ShipDate > (GETDATE()-6) AND Orders.PaymentAmount = Orders.Total_Payment_Received
Group By Orders.OrderID

I want to SELECT more columns, but I cannot because I am using the SUM function so it doesn't work, how can I separate the sum functions from the rest of the attributes so the CSV file has all of the columns plus the SUM of Trackingnumber.Shipment_Costin one column?

我想选择更多的列，但我不能，因为我使用的是 SUM 函数，所以它不起作用，我如何将 sum 函数与其余属性分开，以便 CSV 文件包含所有列加上 SUMTrackingnumber.Shipment_Cost在一栏中？

回答by Peter Majeed

You can use a subquery.

您可以使用子查询。

SELECT *
    ,(
        SELECT SUM(Shipment_Cost)
        FROM Trackingnumbers
        WHERE Trackingnumbers.OrderID = Orders.OrderID
    ) AS Shipping_Cost
FROM Orders
WHERE Orders.OrderStatus = 'Shipped'
    AND Orders.ShipDate > (GETDATE()-6)
    AND Orders.PaymentAmount = Orders.Total_Payment_Received

回答by MPelletier

The idea here is that you are using an aggregation query (with GROUP BY). That means the columns you return should normally be either the result of an aggregate function or something with which you are grouping.

这里的想法是您正在使用聚合查询（使用 GROUP BY）。这意味着您返回的列通常应该是聚合函数的结果或与您分组的内容。

Depending on the platform you are using, some have a "First" function which can be useful.

根据您使用的平台，有些具有“第一”功能，这可能很有用。

回答by Gabriel

There's a couple different ways to get it.

有几种不同的方法可以获得它。

I. Apply an aggregate function to every column that you want to have in your result

I. 将聚合函数应用于您希望在结果中包含的每一列

SELECT Orders.OrderID, MAX(Orders.ShipDate) As ShipDate, MAX(Orders.OrderStatus) As OrderStatus, SUM(Trackingnumbers.Shipment_Cost) AS Shipping_Cost
FROM Orders 
INNER JOIN Trackingnumbers
ON Orders.OrderID = TrackingNumbers.OrderID
WHERE Orders.OrderStatus = 'Shipped' AND Orders.ShipDate > (GETDATE()-6) AND Orders.PaymentAmount = Orders.Total_Payment_Received
Group By Orders.OrderID

II. Put all the field in the GROUP BY clause

二、将所有字段放在 GROUP BY 子句中

SELECT Orders.OrderID, Orders.ShipDate, Orders.OrderStatus, SUM(Trackingnumbers.Shipment_Cost) AS Shipping_Cost
FROM Orders 
INNER JOIN Trackingnumbers
ON Orders.OrderID = TrackingNumbers.OrderID
WHERE Orders.OrderStatus = 'Shipped' AND Orders.ShipDate > (GETDATE()-6) AND Orders.PaymentAmount = Orders.Total_Payment_Received

III. Use a subquery

三、使用子查询

SELECT *, (SELECT SUM(Trackingnumbers.Shipment_Cost)
FROM Trackingnumbers
WHERE TrackingNumbers.OrderID=Orders.OrderID) AS Shipping_Cost
WHERE Orders.OrderStatus = 'Shipped' AND Orders.ShipDate > (GETDATE()-6) AND    Orders.PaymentAmount = Orders.Total_Payment_Received FROM Orders

回答by xuanyuanzhiyuan

You can do another query base on your query result, like this:

您可以根据查询结果执行另一个查询，如下所示：

select b.aaa,b.bbb from (
    SELECT Orders.OrderID,SUM(Trackingnumbers.Shipment_Cost) AS Shipping_Cost
FROM Orders 
INNER JOIN Trackingnumbers
ON Orders.OrderID = TrackingNumbers.OrderID
WHERE Orders.OrderStatus = 'Shipped' AND Orders.ShipDate > (GETDATE()-6) AND Orders.PaymentAmount = Orders.Total_Payment_Received
Group By Orders.OrderID) as a,Orders b where a.OrderID = b.OrderID.