SQL Select from Different Source

1
votes

I really don't know what I'm doing with SQL, but I have two tables. I know it's possible that you can pull data from one table and add it to another table if columns are equivalent. So I wanted something like:

SELECT Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity)
WHERE Orders.OrderID=OrderDetails.OrderID
AS COGS

Basically, the orders and orderdetails table are two separate tables but the orderdetails has the OrderID element which associates it to an order in the orders table. So my function is where the Orders.OrderID=OrderDetails.OrderID, that is where to implement the sum, I want it for each order. I was able to find a sample code that does this task with a lot of excess code:

SELECT orders.orderid,
       orders.cogs
FROM   (SELECT orders.orderid,
               orderdetails.cogs
        FROM   (SELECT orders.orderid AS orderid
                FROM   (((orders WITH(nolock)
                          LEFT JOIN paymentmethods WITH(nolock)
                            ON orders.paymentmethodid =
                               paymentmethods.paymentmethodid)
                         LEFT JOIN shippingmethods WITH(nolock)
                           ON orders.shippingmethodid =
                              shippingmethods.shippingmethodid)
                        LEFT JOIN customers WITH(nolock)
                          ON orders.customerid = customers.customerid)
                GROUP  BY orders.orderid) orders
               INNER JOIN (SELECT
                          orders.orderid
                          AS orderid,
                                  COUNT(orderdetails.orderdetailid)
                          AS
                                                            orderdetails_count
                                                            ,
                                  SUM(orderdetails.quantity)
                                                            AS quantity,
                                  SUM(orderdetails.vendor_price *
                          orderdetails.quantity) AS
                                                            cogs,
                                  CASE
                                    WHEN SUM(vendor_price) IS NULL THEN NULL
                                    ELSE SUM(( CASE
                                                 WHEN orderdetails.productcode
                                                      LIKE
                                                      'DSC-%'
                                               THEN
                                                 orderdetails.productprice
                                                 - Isnull(
                                                 orderdetails.vendor_price, 0)
                                                 ELSE orderdetails.productprice
                                                      -
                                                      orderdetails.vendor_price
                                               END ) * orderdetails.quantity)
                                  END
                          AS
                                                            profit,
                                  CASE SUM(orderdetails.productprice *
                          orderdetails.quantity)
                                    WHEN 0 THEN 0
                                    ELSE Round(( ( SUM(orderdetails.productprice
                                                       *
                                                       orderdetails.quantity)
                                                   - SUM(
                                                     orderdetails.vendor_price *
                                                     orderdetails.quantity) ) /
SUM(
             orderdetails.productprice
             *
             orderdetails.quantity) )
* 100,
1)
END
AS
           profitmargin
FROM   (((orders WITH(nolock)
LEFT JOIN paymentmethods WITH(nolock)
ON orders.paymentmethodid =
paymentmethods.paymentmethodid)
LEFT JOIN shippingmethods WITH(nolock)
ON orders.shippingmethodid =
shippingmethods.shippingmethodid)
LEFT JOIN customers WITH(nolock)
ON orders.customerid = customers.customerid)
LEFT JOIN orderdetails WITH(nolock)
ON orders.orderid = orderdetails.orderid
WHERE  orders.orderstatus <> 'Cancelled'
GROUP  BY orders.orderid) orderdetails
ON orders.orderid = orderdetails.orderid) orders
ORDER  BY orders.orderid DESC

This basically delivers the orders and their COGS for each order into the table. But every time I try to delete a line of excess code I get an error. Things like ShippingMethodID are unnecessary. Please help.

EDIT:

SELECT Orders.OrderID, Orders.SalesRep_CustomerID, Orders.Total_Payment_Received, Orders.SalesTax1, SumDetails.COGS, ISNULL(Total_Shipping_Cost.Shipping_Cost,0) as Shipping_Cost

FROM Orders

JOIN

(SELECT OrderID, Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity)

AS COGS

FROM OrderDetails

GROUP BY OrderID)

AS SumDetails

ON SumDetails.OrderID=Orders.OrderID LEFT

JOIN

(SELECT OrderID, SUM(Shipment_Cost)

AS Shipping_Cost

FROM Trackingnumbers

GROUP BY OrderID)

AS Total_Shipping_Cost

(SELECT CASE

WHEN Orders.ShippingMethodID

in (19, 20, 21, 25, 26, 27, 28, 30, 31, 502)

THEN 5

ELSE 0

END)

AS ServiceCharge

ON Total_Shipping_Cost.OrderID = Orders.OrderID

WHERE Orders.OrderStatus = 'Shipped'

AND Orders.ShipDate > (GETDATE()-6)

AND Orders.PaymentAmount = Orders.Total_Payment_Received

4
sqlsql-serverselectsumequals
Is this Microsoft SQL Server? - UnhandledExcepSean
That is possibly the most painful SQL I've ever seen - UnhandledExcepSean
@SpectralGhost I'm sure it is, I can't edit it for the life of me - henryaaron
I answered below and hopefully provided something that can help with that original query. - UnhandledExcepSean

4 Answers

2
votes

That's what JOINs are for

In your example, this is what you would be doing

SELECT  Orders.OrderId, SUM(OrderDetails.Vendor_Price * OrderDetails.Quantity)
FROM    Orders
        INNER JOIN OrderDetails
          ON Orders.OrderId = OrderDetails.OrderId
GROUP BY Orders.OrderId

There are tons of articles online that you can read up on Joins and aggregating data

Link to an article explaining joins http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

Here's an article on GROUP BY: http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server

1
votes

To answer your question

SELECT Orders.OrderID,Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity) AS COGS
FROM Orders
INNER JOIN OrderDetails ON Orders.OrderID=OrderDetails.OrderID
GROUP BY Orders.OrderID

I believe this is equivalent to what you have... I'm hoping that will help because I'm thinking what you are wanting may not take into account the current logic of what is going on.

SELECT
    orders.orderid AS orderid,
    COUNT(orderdetails.orderdetailid) AS orderdetails_count,
    SUM(orderdetails.quantity) AS quantity,
    SUM(orderdetails.vendor_price * orderdetails.quantity) AS cogs,
    CASE
        WHEN SUM(vendor_price) IS NULL THEN NULL
        ELSE SUM(
                    CASE
                        WHEN orderdetails.productcode LIKE 'DSC-%' THEN (orderdetails.productprice - Isnull(orderdetails.vendor_price, 0))* orderdetails.quantity
                        ELSE (orderdetails.productprice-orderdetails.vendor_price)* orderdetails.quantity
                    END
                )
    END AS profit,
    CASE SUM(orderdetails.productprice *orderdetails.quantity)
        WHEN 0 THEN 0
        ELSE Round(((SUM(orderdetails.productprice * orderdetails.quantity) - SUM(orderdetails.vendor_price * orderdetails.quantity)) / SUM(orderdetails.productprice * orderdetails.quantity)) * 100,1)
    END AS profitmargin
FROM orders WITH(nolock)
LEFT JOIN paymentmethods WITH(nolock) ON orders.paymentmethodid = paymentmethods.paymentmethodid
LEFT JOIN shippingmethods WITH(nolock) ON orders.shippingmethodid = shippingmethods.shippingmethodid
LEFT JOIN customers WITH(nolock) ON orders.customerid = customers.customerid
LEFT JOIN orderdetails WITH(nolock) ON orders.orderid = orderdetails.orderid
WHERE  orders.orderstatus <> 'Cancelled'
GROUP  BY orders.orderid
ORDER  BY orders.orderid DESC
1
votes

This should get you started:

SELECT Orders.OrderID,
SumDetails.COGS
FROM Orders
JOIN 
    (SELECT OrderID,
    Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity) AS COGS
    FROM OrderDetails
    GROUP BY OrderID) AS SumDetails
ON SumDetails.OrderID=Orders.OrderID

EDIT: To add in the other columns from Orders (and this is why I like the subquery approach - they don't have to be in a GROUP BY):

SELECT Orders.OrderID,
Orders.SalesRep_CustomerID,
Orders.Total_Payment_Received,
Orders.S‌​alesTax1,
SumDetails.COGS,
ISNULL(Total_Shipping_Cost.Shipping_Cost,0) as Shipping_Cost
FROM Orders
JOIN 
    (SELECT OrderID,
    Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity) AS COGS
    FROM OrderDetails
    GROUP BY OrderID) AS SumDetails
ON SumDetails.OrderID=Orders.OrderID
LEFT JOIN
    (SELECT OrderID,
    SUM(Shipment_Cost) AS Shipping_Cost 
    FROM Trackingnumbers
    GROUP BY OrderID) AS Total_Shipping_Cost 
ON Total_Shipping_Cost.OrderID = Orders.OrderID  
WHERE Orders.OrderStatus = 'Shipped' 
AND Orders.ShipDate > (GETDATE()-6)
AND Orders.PaymentAmount = Orders.Total_Payment_Received
0
votes

Are you looking for something like this:

SELECT Orders.OrderID, Sum(OrderDetails.Vendor_Price * OrderDetails.Quantity)
FROM Orders
INNER JOIN OrderDetails on Orders.OrderID = OrderDetails.OrderID
WHERE ....
GROUP BY Orders.OrderID