CODEWITHSUNDEEP
sqlsql-serverstored-procedures
meet270
meet270

Reputation: 1

SQL query based on aggregate and join

I am new to SQL and I am trying to join two tables by performing aggregate and join functions. I am using Orderdetails and PurchaseOrderDetails tables to join based on OrderID.

I want to compare the sum on quantity in the OrderDetails table and sum on quantity in the PurchaseOrderDetails table and sum(PurchaseOrderDetails.quantity) != sum(OrderDetails.quantity) based on particular orderID and also join them based on orderID.

I have following two tables:

OrderDetails table design 

SELECT TOP 1000 [lineID]
      ,[orderID]
      ,[itemNumber]
      ,[itemDesc]
      ,[qty]
      ,[price]
      ,[priceType]
      ,[contract]
      ,[supplierPartID]
      ,[uom]
      ,[classDomain]
      ,[classValue]
      ,[comments]
      ,[shipping]
      ,[tax]
      ,[reqDeliveryDate]
      ,[fillInfo]
      ,[invoiceID]
      ,[Version]
      ,[productid]
      ,[eta]
      ,[lineOrderID]
  FROM [bfdb].[dbo].[OrderDetails]

and PurchaseOrderDetails table design is:

SELECT TOP 1000 [podID]
      ,[poID]
      ,[distPN]
      ,[mfrID]
      ,[itemNumber]
      ,[itemName]
      ,[qty]
      ,[price]
      ,[invoiceID]
      ,[orderID]
      ,[itemReceived]
      ,[qtyReceived]
      ,[distConf]
      ,[distFillStatus]
      ,[distLastUpdate]
      ,[distTracking]
      ,[distFillDesc]
      ,[Version]
      ,[productid]
      ,[lineOrderID]
  FROM [bfdb].[dbo].[PurchaseOrderDetails]

I tried the following query but getting the incorrect result. Suppose if it has 1 row in the OrderDetails table and 2 rows in the PurchaseOrderDetails, then the sum for OrderDetails becomes double after joining. Same with 1 row in PurchaseOrderDetails table if I have more than 1 row in OrderDetails table for that

SELECT
    od.orderID, SUM(pod.qty) as Totalqty, SUM(od.qty) as od_qty
FROM
    PurchaseOrderDetails pod
INNER JOIN 
    OrderDetails od ON od.orderID = pod.orderID AND pod.orderID = 15506
GROUP BY
    od.orderID

Here is the snapshot of my data

PurchaseOrderDetails

OrderDetails

Upvotes: 0

Views: 72

Answers (2)

Akber Ali
Akber Ali

Reputation: 13

You need to simply change the AND condition to a WHERE clause, try this code below:

SELECT od.orderID, SUM(pod.qty) as Totalqty, SUM(od.qty) as od_qty FROM PurchaseOrderDetails pod INNER JOIN OrderDetails od ON od.orderID = pod.orderID WHERE pod.orderID = 15506 GROUP BY od.orderID

Upvotes: 0

rohitvats
rohitvats

Reputation: 1851

You need to first sum the quantities by orderid for each of these two tables separately and then join. Try this:

select od.orderID, pod.qty as Totalqty, od.qty as od_qty
from (
    select orderid, sum(pod.qty) as qty
    from PurchaseOrderDetails
    group by orderid
) pod
inner join (
    select orderid, sum(pod.qty) as qty
    from OrderDetails
    group by orderid
) od on od.orderid = pod.orderid

Upvotes: 1

Related Questions