2 sum for the same field in 1 query

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

2 sum for the same field in 1 query

Author

Topic

gorgo
Starting Member

2 Posts

Posted - 2010-09-15 : 05:30:12

Hi guys,

I have table [YTD-2](PRODUCT, YEAR, TYPE, NET PREM)
PRODUCT: list of products
YEAR : could be 2009 or 2010.
TYPE : could be New, Renew, Endorsment, Cancellation.
Net Premium is numeric.

i need a query to generate, by product, by year, NetNew, NetAll
TotalNETNew: sum of NET PREMIUM WHERE TYPE=new
TOTALNET:sum for all types including new.

i wrote the following query:

Select product, year, SUM([Net Prem ]) NetAll,(Select SUM([Net Prem ])
From dbo.[YTD-2]
Where Type = 'New') NetNew
From dbo.[YTD-2]
Group by Product, year

However the Netall was correct, but the NetNew was having the same value for all products.
What is still missing in the query

Thank you in advanced.

Ancy
Starting Member

23 Posts

Posted - 2010-09-15 : 05:59:57

try this instead

Select product, year, SUM(case when type = 'New'then [Net Prem ] else 0 end) NetAll,SUM([Net Prem ])
From dbo.[YTD-2]
Group by Product, year

gorgo
Starting Member

2 Posts

Posted - 2010-09-15 : 07:05:11

Thank you..

Its perfect ..

Ancy
Starting Member

23 Posts

Posted - 2010-09-15 : 07:47:27

you are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources