Help with 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with SUM

Author

Topic

jdobson
Starting Member

1 Post

Posted - 2009-11-24 : 23:40:04

Hi there,

I need some help with the statement below, I am trying to sum my [Cost], but I can't because it includes a subquery. Can someone help me out? I am using MS SQL 2005

select
c.cmp_name [Cust],
o.ord_revtype1 [Office],
o.ord_booked_revtype1 [Sales Rep],
sum(o.ord_totalcharge) [Revenue],

isnull((select sum(isnull(pyd_grossamount,0)) from paydetail p
where not pyt_itemcode in ('comchk','comfee') and o.ord_hdrnumber = p.ord_hdrnumber
group by p.ord_hdrnumber),0) [Cost]

from orderheader o, company c
where o.ord_billto = c.cmp_id
group by c.cmp_name, o.ord_booked_revtype1, o.ord_revtype1, o.ord_hdrnumber
order by sum(o.ord_totalcharge) desc

If this belongs in another forum, I apologize - this is my first post here. Any advice is welcome.

Thank you!

Joe

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-25 : 00:09:04

try like this


SELECT 
c.cmp_name [Cust],
o.ord_revtype1 [Office],
o.ord_booked_revtype1 [Sales Rep],
r.Revenue,
c.cost
FROM 
orderheader o
INNER JOIN company c ON  o.ord_billto = c.cmp_id
INNER JOIN 
(SELECT ord_hdrnumber,SUM(ord_totalcharge) AS Revenue 
FROM orderheader GROUP BY ord_hdrnumber)r ON o.ord_hdrnumber = r.ord_hdrnumber
INNER JOIN 
(SELECT ord_hdrnumber,SUM(ISNULL(pyd_grossamount,0)) AS cost
FROM paydetail
WHERE pyt_itemcode NOT IN ('comchk','comfee') 
GROUP BY ord_hdrnumber)AS c ON o.ord_hdrnumber = c.ord_hdrnumber
ORDER BY [Revenue]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources