Stuck on Query

• dan.rosenberger

  SSC Rookie

  Points: 44

  More actions

  July 30, 2009 at 7:19 am

  #224057

  So I'm a bit stuck on this problem and wanted to see if any of the gurus out there had any thoughts of where I might be going wrong

  End Result: I need to create an SQL query from a master/detail relationship to show me records where the SUM of the detail record's "Amount Paid" field equal or are more than 75% of the equipments master record field "Equipment Total Cost". This is in effort to display a list of pieces of equipment where it makes sense to purchase rather than continue to rent and avoid over paying the total value cost.

  In my example, I have a master / detail table relationship (linked on ID#)

  Master table: ID#, Equipment Name, Equipment Total Cost

  Detail Table: ID#, Invoice Number, Amount Paid

  I think I'm on the right path by doing a "SUM (Amount Paid) As "Invoice Total Paid" (I know SUM will just add, i still need to /divide by Total cost *100 for percent, but trying to just get the addition idea down) ....and then adding the other fields into a "GROUP BY" clause at the bottom, but for some reason, the addition of the "Amount Paid" field from the detail table isn't happening...the SUM doesn't seem to be doing anything. The query runs just fine, and it makes a new field in my result set, but the SUM never happens, it just returns what the original "Amount Paid" field was. As I mentioned, i had to put the remaining fields into a "GROUP BY" at the end because SQL tells me they either need to be in a group by or an aggregate, and this might be where my problem is....

  Any help would be so very much appreciated!

  Thanks!

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 30, 2009 at 8:22 am

  #1032407

  Since you didn't provide the code you are currently using I'll just give you what I would do:

  SELECT

  MT.equipment_name,

  MT.total_cost,

  SUM(DT.amount_paid) AS total_paid,

  (SUM(DT.amount_paid)/MT.total_cost) * 100 AS percent_of_total_cost_paid

  FROM

  master_table MT JOIN

  detail_table DT ON

  MT.id = DT.id

  GROUP BY

  MT.equipment_name,

  MT.total_cost

  /*

  Having is like a WHERE AFTER the grouping is done

  */

  HAVING

  ((SUM(DT.amount_paid)/MT.total_cost) * 100) >= 75

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• dan.rosenberger

  SSC Rookie

  Points: 44

  More actions

  July 30, 2009 at 9:17 am

  #1032452

  Woot! everything works as expected now! Thank you so much!