Query help needed on a one to many sum

[devphreak]

Hi,



n00b to the forums here and it's been years since I did any SQL but am finally back in it

I have a query where in Table 1 I need to sum up two fields, the Table 2 I need to sum up four fields. This is a one to many where in Table 1 there is an order number that exists many times and in Table 2 the order number exists only once. Here's the query that I have so far:

SELECT Sum([Jan - Shipped and Unpaid].[Total QS Cost]) AS [SumOfTotal QS Cost], Sum([Jan - Shipped and Unpaid].[Total QS Price]) AS [SumOfTotal QS Price], Sum(Orders.ProductTotal) AS SumOfProductTotal, Sum(Orders.TaxTotal) AS SumOfTaxTotal, Sum(Orders.ShippingTotal) AS SumOfShippingTotal, Sum(Orders.GrandTotal) AS SumOfGrandTotal
FROM [Jan - Shipped and Unpaid] INNER JOIN Orders ON [Jan - Shipped and Unpaid].OrderNumber = Orders.OrderNumber;

This duplicates the records in Table 2 and gives me invalid sums. I believe there needs to be a DISTINCT keyword somehow in Table 2, but with the years that have passed since I did any SQL, I'm a little stuck on this one

Any help is greatly appreciated!

[jzwp11]

I'm not real clear on your structure, but it sounds like you want to sum 4 fields (not records) in Table2. That query would look like this

query name: qrySumOfFieldsInTable2
SELECT orderno, (field1+field2+field3+field4) as table2sum
FROM Table2

Since you only have 1 record per order number in table2, you would not need any grouping. The same is not true for table 1 since you have many records for each orderno. First, a query to sum the two fields

query name: qrySumOfFieldsInTable1
SELECT orderno, (field1+field2) as table1sum
FROM Table1

Now, since you have multiple records for each order, we have to group those records. I will assume that you want the sum of the summed fields in this query

query name: qrySumOfSumsForRecordsinTable1
SELECT orderno, SUM(table1sum) as SumOftable1Sums
FROM qrySumOfFieldsInTable1
GROUP BY orderno

Since the above query will give us 1 record per orderno, we can join that back to table2

SELECT (table2sum + SumOftable1Sums) as GrandTotal
FROM qrySumOfFieldsInTable2 INNER JOIN qrySumOfSumsForRecordsinTable1 ON qrySumOfFieldsInTable2.orderno=qrySumOfSumsForReco rdsinTable1.orderno

[devphreak]

Thanks!

I actually found the answer on the MSDN forums:

SELECT Orders.OrderNumber, 
SUM(Orders.ShippingTotal) AS SumOfShippingTotal, 
SUM(Orders.GrandTotal) AS SumOfGrandTotal, 
OD.CountOfOrderNumber, 
OD.SumOfTotalQSPrice
FROM Orders INNER JOIN
    (SELECT OrderNumber,
      SUM([Total QS Price]) AS SumOfTotalQSPrice, 
      COUNT(*) AS CountOfOrderNumber
      FROM [Jan - Shipped and Unpaid]
      GROUP BY [Jan - Shipped and Unpaid].OrderNumber) AS OD
ON Orders.OrderNumber = OD.OrderNumber
GROUP BY Orders.OrderNumber, OD.CountOfOrderNumber, OD.SumOfTotalQSPrice;

[jzwp11]

Glad you found a solution! Good luck with your project.