Header/Detail Totals Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Header/Detail Totals Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-09 : 08:53:42

Mike writes "I need to generate a single row of totals for each invoice date.
e.g. Invoice Date, Total Sales $, Total LBS, Total Freight

Invoice Header contains:

Invoice Number
Invoice Date
Total LBS
Total Freight

Invoice Detail contains:

Invoice Number
Line Number
Quantity
Unit Price

I can easily get the two totals by date from the Invoice Header and I can easily get the Sales total by date from the the Invoice Detail, but I want a single query to generate all three totals. So far, my queries have resulted in correct totals from the Detail table with the totals from the Header multiplied by the number of line items in the detail table."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-09 : 08:54:09

Look in Books Online under "CUBE", "ROLLUP", and "GROUPING", they should work nicely.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-09 : 09:36:18

you cannot join two table with differnet primary keys and just sum up all columns -- that will not return correct data, as you've seen. you need to somehow "massage" both tables so that they have the same PK, and then join them together.

The common thread is -- InvoiceNumber. it's already the PK of the Header table, so let's write a query that returns 1 row per InvoiceNumber from the Details table:


select 
  InvoiceNumber, sum(Quantity * UnitPrice) as DetailsSum
from 
  InvoiceDetails
group by 
  InvoiceNumber

as i always say, look at the above, test it, run it, edit it, understand it, make sure it works befor moving to the next step ... but you must be absolutely sure it returns exactly 1 row per Invoice Number.

Now that you have that, you can write your final query:


select 
   a.InvoiceDate, 
   sum(a.[Total LBS]) as TotalLBS, 
   sum(a.[Total Freight]) as TotalFreight, 
   sum(b.DetailsSum) as DetailsTotal
from 
  InvoiceHeaders a
inner join
  (the above SQL summarizing the Details table) b
on
  a.InvoiceNumber = b.InvoiceNumber
group by
  a.InvoiceDate

I hope this helps and makes sense .. it's the key to writing aggregate sql statemetns -- you MUST ensure that all joined recordsets that have columns you wish to SUM have the same "virtual" primary key.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources