Trouble with Grouping and Sums

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

Development Tools

Reporting Services Development

Trouble with Grouping and Sums

Author

Topic

angeja
Starting Member

2 Posts

Posted - 2009-02-11 : 10:57:17

Hello All-
I have been struggling with this for a few days and it really shouldn't be this hard...
I have a query which pulls master/detail type data. The master data has the PO numbers that I need summed in the group footer, but when the report is summing the detail data instead.
The data all comes from 1 query that i can post if necessary.

Sample Data.
Group 1 = Month
Header PO# PO Date TotAmt tax AmtPaid TaxPaid
Line Line Qty Each

PO1234 01/01/09 $500 $75 100 50
1 1 100
2 2 200
3 1 200

Header- PO2345 01/01/09 $300 $15 300 15
Line 1 1 300

the correct monthly totals should be:
Monthly Total = $800 $90 $400 $65
Instead I am receiving
Monthly Total = $1800 $240 $600 $165
The group footer is summing the TotAmount for each detail record, and when there should only be 1 amount at that level per PO. This important because the paid paid amounts are not kept at a detail(line) level only at the header level

Any help is greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-11 : 11:22:08

one way of doing this is to include a temp table to capture distinct orders as follows


CREATE TABLE #DISTINCT_ORDERS
(
Header varchar(30),
LineID int
)

INSERT INTO #DISTINCT_ORDERS
SELECT Header,MIN(LineID) AS LineID
FROM detailtable

then left join with this table in final query on header and lineid and use case when lineid is not null condition to count each header only once.

angeja
Starting Member

2 Posts

Posted - 2009-02-11 : 13:02:57

Thanks for the reply I will give it a try.
Could you just elaborate a bit on the case when line id is null.
This is a little fuzzy to me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 02:32:01

in the main query you use for report use like this


SELECT q.relevant fields...,CASE WHEN do.LineID IS NULL THEN 0 ELSE 1 END AS Inc
FROM
(
your current query)q
LEFT JOIN #DISTINCT_ORDERS do
ON do.Header=q.Header
AND do.LineID=q.Line 

then in report use this as total expression

=Sum(IIF(Fields!Inc.Value=1,Fields!LineQty.value,0))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources