What is the proper way to do one to many joins with aggregates

• billross

  SSC Eights!

  Points: 892

  More actions

  March 8, 2010 at 4:35 am

  #217502

  I have an Orders table and an Orders_details table. The Orders table has totals for the Sale_amt in the detail table.

  ORDERS_TABLE

  Order_number

  Total_Sale_amt

  ORDERS_DETAILS

  Order_number

  Line_Item_ID

  Sale_amt

  One Orders_table can have many line items in the Orders_details table.

  I am concerned that the total in the Orders_table is out of synch with the line items. I would like a query that compares the sum of the line items of an order with the total in the header, and if it they are not equal, it shows:

  Order_number, Total_Sale_amt, Sum(Sale_amt)

  Can that be done in a single query?

  Thanks.

  "What I lack in youth I make up for in immaturity!"
  Please visit my music site at http://woundedego.com

• aneel.c

  SSC-Addicted

  Points: 467

  More actions

  March 8, 2010 at 4:47 am

  #1129923

  Hope this query would solve your problem!!

  select Order_number,Total_Sale_amt, sum(Sale_amt) from

  ORDERS_TABLE OT

  inner join ORDERS_DETAILS OD

  on OT.Order_number = OD.Order_number

  group by Order_number,Total_Sale_amt