CODEWITHSUNDEEP
sqlselectsum
henryaaron
henryaaron

Reputation: 6202

SQL SELECT multiple with sum

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

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_Cost in one column?

Upvotes: 4

Views: 46044

Answers (4)

Gabriel
Gabriel

Reputation: 3783

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

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

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

Upvotes: 2

xuanyuanzhiyuan
xuanyuanzhiyuan

Reputation: 3999

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.

Upvotes: 0

Peter Majeed
Peter Majeed

Reputation: 5362

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

Upvotes: 9

MPelletier
MPelletier

Reputation: 16697

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.

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

Upvotes: 3

Related Questions