Report 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 2005 Forums

Transact-SQL (2005)

Report Query

Author

Topic

JasonJanofsky
Starting Member

5 Posts

Posted - 2007-01-28 : 01:01:41

Hi,

I am trying to create a report from a table like the following:

ID PolicyNumber ProdID
____ _____________ ________
1 20 1
2 Null 1
3 25 2
4 30 2
5 Null 2

Basically I am trying to get a result set that looks similar to this:

ProdID TotalPolicys TotalPolicys with PolicyNumber is Null
______ ____________ ____________
1 2 1
2 3 2

I will be running a percentage on the two numbers at some point, so the end product would be like:

ProdID Ratio
______ _______
1 .50
2 .75

I have thought of using some kind of loop to iterate though each prodID to get the ratios into another table but I have a feeling one of you awesome SQL guys might have a way to get this done.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-28 : 01:22:19

[code]
declare @table table
(
id int,
PolicyNumber int,
ProdID int
)

insert into @table
select 1, 20, 1 union all
select 2, NULL, 1 union all
select 3, 25, 2 union all
select 4, 30, 2 union all
select 5, NULL, 2

select ProdID,
TotalPolicys = count(*),
[Total Policy with PolicyNumber is not null] = count(PolicyNumber),
[Total Policy with PolicyNumber is null] = count(*) - count(PolicyNumber),
Ratio = count(PolicyNumber) * 1.0 / count(*)
from @table
group by ProdID
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources