SQL - a classical summation/optimization problem

# Thread: SQL - a classical summation/optimization problem

1. Junior Member
Join Date
Dec 1969
Posts
2

## 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. Senior Member
Join Date
Dec 1969
Posts
7,686

## 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
•