SQL - a classical summation/optimization problem

Results 1 to 2 of 2

Thread: SQL - a classical summation/optimization problem

  1. #1
    Join Date
    Dec 1969

    Default SQL - a classical summation/optimization problem

    Hello! <BR><BR>What I have here is a classical summation/optimization <BR>problem. The problem is below, in a simplified form. <BR><BR>We have a table of billings, T_billings. Every billing has a number, BillingID (plus lots of other irrelevant stuff). <BR><BR>Every billing contains lines from T_billed_lines. <BR><BR>Every line here has a sum, LineSum, a LineID, and, <BR>if the line has been billed, a BillingID. <BR><BR>Now, I need a report (surprise!) that includes for every LineID: <BR><BR>BillingID - LineID - LineSum - BillingSum. <BR><BR>BillingSum is the sum of all the LineSums that belongs <BR>to that lines BillingID. This is (for the sake of <BR>the example) not saved in the T_billings. <BR><BR>-------- <BR><BR>The obvious (and slow) way to do this is <BR><BR>SELECT T_billings.BillingID, T_billed_lines.*, <BR>(SELECT SUM(LineSum) <BR>From T_billed_lines tbl JOIN T_billings tb <BR>on tbl.BillingID = tb.BillingID <BR>WHERE tbl.BillingID = T_billings.BillingID) <BR>as BillingSum <BR><BR>FROM T_billings INNER JOIN T_billing ON <BR>T_billings.BillingID = T_billed_lines.BillingID <BR><BR>--------- <BR><BR>So, we make a sum for every frigging LineID, although <BR>it would&#039;ve been enough to do it only for every BillingID. <BR><BR>The obvious solution is to precalculate the sums into <BR>a temp. table and then look them up fast according to <BR>BillingID. <BR><BR>BUT: I HAVE TO USE A VIEW!!! (Access reports blah blah) <BR>So that is not an option... <BR><BR>So, my question is: Is there any clever SQL that will <BR>do this without the SUM for every LineID ? I guess I can <BR>base a view on an SP... but it would be cleaner to just <BR>do one view. It works now, it is just so slow... <BR><BR><BR>Any suggestions ? <BR><BR>------- <BR>Don&#039;t use your head - and you&#039;ll walk alot <BR>

  2. #2
    Join Date
    Dec 1969

    Default COMPUTE BY

    Check Books Online for the COMPUTE (BY) statement. That&#039;s what you need.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts