Sum a Sum ... - 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

General SQL Server Forums

New to SQL Server Programming

Sum a Sum ...

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-03-16 : 16:02:58

Hello I have this query:

select salesline.salesid, inventtable.itemid, inventposting.ledgeraccountid,
sum(salesline.lineamount) as Saleslinesum
from salesline inner join inventtable on
salesline.dataareaid = inventtable.dataareaid
and salesline.itemid = inventtable.itemid inner join
inventposting on inventtable.itemgroupid = inventposting.itemrelation
and inventtable.dataareaid = inventposting.dataareaid
where salesline.remainsalesphysical > 0 and inventposting.inventaccounttype = '23'
group by salesline.salesid, inventtable.itemid, inventposting.ledgeraccountid

This produces results like so:
SalesIdItemIdLedgerAccountIdSalesLineSum
000001123456220004521.00
000002125421220009521.23
000003542188220014172.02
000004454418220014578.33

Now I need to sum SalesLineSum by LedgerAccountId but I am not quite sure how to code this. The results should only sum the same LedgerAccountId. Here is how I want the results to be after this is summed by LedgerAccountId:

0000011234562200014042.23
000002542188220018750.35

Any help greatly appreciated.

Thank you in advance.

Regards

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-03-16 : 17:02:49

Looks like I have stumped the Guru's!

Anyways, I tried using this compute statement but it gives me multiple tables that produce multiple results. I need one table with multiple results:

select salesline.salesid, inventtable.itemid, inventposting.ledgeraccountid,
sum(salesline.lineamount) as Saleslinesum
from salesline inner join inventtable on
salesline.dataareaid = inventtable.dataareaid
and salesline.itemid = inventtable.itemid inner join
inventposting on inventtable.itemgroupid = inventposting.itemrelation
and inventtable.dataareaid = inventposting.dataareaid
where salesline.remainsalesphysical > 0 and inventposting.inventaccounttype = '23' and salesline.dataareaid = 'osg'
group by salesline.salesid, inventtable.itemid, inventposting.ledgeraccountid
order by inventposting.ledgeraccountid
compute sum(sum(salesline.lineamount)) by inventposting.ledgeraccountid

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 17:04:36

Is there a criteria for which itemid and salesId you want to display. Meaning, If you look at the eg you have given, against ledgeraccountid=22000, you have taken salesid=000001 and itemid=123456. Why did you pick this and not 000002 and 125421???

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-03-16 : 17:06:47

quote:
Originally posted by sakets_2000

Is there a criteria for which itemid and salesId you want to display. Meaning, If you look at the eg you have given, against ledgeraccountid=22000, you have taken salesid=000001 and itemid=123456. Why did you pick this and not 000002 and 125421???

This really does not matter. The salesId and itemid are not relevant to the data I want.

If the results displayed only the ledgeraccount and the totals that match the ledger accounts that would be fine.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 17:10:57

[code]select
max(salesline.salesid),
max(inventtable.itemid),
inventposting.ledgeraccountid,
sum(salesline.lineamount) as Saleslinesum
from
salesline inner join inventtable on salesline.dataareaid = inventtable.dataareaid and salesline.itemid = inventtable.itemid
inner join inventposting on inventtable.itemgroupid = inventposting.itemrelation and inventtable.dataareaid = inventposting.dataareaid
where
salesline.remainsalesphysical > 0 and inventposting.inventaccounttype = '23'
group by
inventposting.ledgeraccountid[/code]

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-03-17 : 10:35:01

Thank you sakets for your reply. I have gone another route with this and am summing this using SSRS. Forgot to thank you for your efforts.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 10:37:52

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources