Newbie SQL 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)

Newbie SQL query

Author

Topic

alanmac
Starting Member

26 Posts

Posted - 2010-11-10 : 09:18:43

Hi,

I have had a request for a report on a web application I built. In the database there is a field called Phase (int, from 1 to 10) and another called Collected (bit). I have been asked to generate a report showing the following:

Phase Total Percentage

Total would be the count for each phase, and percentage would be the percentage of Total where Collected = true.

I have no idea where to start with this one. Could anyone give me a poke in the right direction, perhaps a similar script?

Many thanks in advance.

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 09:30:34

[code]

select phase,
count(phase),
(count(phase)*100)/sum(case when Collected = 1 then 1 else 0 end)
from table
group by phase

[/code]

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-10 : 09:58:39

quote:

Originally posted by Sachin.Nand



select phase,
count(phase),
(count(phase)*100)/sum(case when Collected = 1 then 1 else 0 end)
from table
group by phase

PBUH

Use 100.0 for accuracy

Madhivanan

Failing to plan is Planning to fail

alanmac
Starting Member

26 Posts

Posted - 2010-11-11 : 02:08:57

Excellent. Thanks you very much indeed. Only one small problem, though. If sum(Collected) = 0, I get a divide by zero exception.

I tried this:

select Phase,
count(NTuserID) as [Total Count]
,(count(NTUserID)*100.0)/case when sum(case when Collected = 1 then 1 else 0 end) = 0 then 1 else sum(case when Collected = 1 then 1 else 0 end) end
from Table
group by phase

but that also threw the same exception.

alanmac
Starting Member

26 Posts

Posted - 2010-11-11 : 03:35:26

Not to worry, I fixed it:

select Phase,
count(NTuserID) as [Total Count],
ISNULL(sum(case when Collected = 1 then 1 else null end), 0)
,ISNULL(round(cast(sum(case when Collected = 1 then 1 else null end) as float)/count(NTUserID)* 100.0, 2), 0) as Percentage
from Table
group by phase

Thanks for all the help

Sachin.Nand

2937 Posts

Posted - 2010-11-11 : 04:50:37

quote:
Originally posted by alanmac

Not to worry, I fixed it:

select Phase,
count(NTuserID) as [Total Count],
ISNULL(sum(case when Collected = 1 then 1 else null end), 0)
,ISNULL(round(cast(sum(case when Collected = 1 then 1 else null end) as float)/count(NTUserID)* 100.0, 2), 0) as Percentage
from Table
group by phase

Thanks for all the help

Much compact


select phase,
count(phase),
(count(phase)*100.0)/IsNull(sum(case when Collected = 1 then 1  end),1)
from table
group by phase

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-11 : 06:27:31

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by alanmac

Not to worry, I fixed it:

select Phase,
count(NTuserID) as [Total Count],
ISNULL(sum(case when Collected = 1 then 1 else null end), 0)
,ISNULL(round(cast(sum(case when Collected = 1 then 1 else null end) as float)/count(NTUserID)* 100.0, 2), 0) as Percentage
from Table
group by phase

Thanks for all the help

Much compact

select phase,
count(phase),
(count(phase)*100.0)/IsNull(sum(case when Collected = 1 then 1 end),1)
from table
group by phase

PBUH

It may give wrong results

Use this

select phase,
count(phase),
(count(phase)*100.0)/nullif(sum(case when Collected = 1 then 1 end),0)
from table
group by phase

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-11-11 : 23:45:33

quote:

select phase,
count(phase),
(count(phase)*100.0)/nullif(sum(case when Collected = 1 then 1 end),0)
from table
group by phase

But it is going to going to give NULL result if the return value for NullIf is Null.Something like
select 100/null

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-12 : 05:12:30

quote:
Originally posted by Sachin.Nand

quote:

select phase,
count(phase),
(count(phase)*100.0)/nullif(sum(case when Collected = 1 then 1 end),0)
from table
group by phase

But it is going to going to give NULL result if the return value for NullIf is Null.Something like
select 100/null

PBUH

Yes that is correct. When 100/0 gives error, you are making it to 100/1 thus giving the result 100 which is wrong. A null value is preferred here

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 06:04:52

quote:
Yes that is correct. When 100/0 gives error, you are making it to 100/1 thus giving the result 100 which is wrong.

Yup you are right.Thanks.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-12 : 06:26:29

quote:
Originally posted by Sachin.Nand

quote:
Yes that is correct. When 100/0 gives error, you are making it to 100/1 thus giving the result 100 which is wrong.

Yup you are right.Thanks.

PBUH

No problem

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources