Stop rounding - 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

Old Forums

CLOSED - General SQL Server

Stop rounding

Author

Topic

bombdrop
Starting Member

5 Posts

Posted - 2006-10-03 : 06:10:23

Im trying to show the percentage breackdown of error stages for a report i'm writting. Problem is it seems to be rounding the percentage how do i stop this many thanks.



declare @ClaimCount int

set @ClaimCount =( select count(*) from claim )

      
 
select
	--Total Claims
	( select count(*) from claim )'Total Claims'
	--AppointmentStage
	,(select count(cause6ref) from claim where cause6ref=1)'Appointment Stage'
	--During Site Inverstigation/Survey
	,(select count(cause6ref) from claim where cause6ref=2)'During Site Inverstigation/Survey'
		--Design Stage
	,(select count(cause6ref) from claim where cause6ref=3)'Design Stage'
	--During Construction
	,(select count(cause6ref) from claim where cause6ref=4)'During Construction'
		--Others
	,(select count(cause6ref) from claim where cause6ref=50)'Others'
	--Non
	,(select count(cause6ref) from claim where cause6ref=0)'Non'



union
	select
	--Total Claims
	(((select count(*) from claim )*100)/ @ClaimCount) 'Total Claims'
	--AppointmentStage
	,(((select count(cause6ref) from claim where cause6ref=1)*100)/ @ClaimCount)  'Appointment Stage'
	--During Site Inverstigation/Survey
	,(((select count(cause6ref) from claim where cause6ref=2)*100)/ @ClaimCount)'During Site Inverstigation/Survey'
		--Design Stage
	,(((select count(cause6ref) from claim where cause6ref=3)*100)/ @ClaimCount)'Design Stage'
	--During Construction
	,(((select count(cause6ref) from claim where cause6ref=4)*100)/ @ClaimCount)'During Construction'
		--Others
	,(((select count(cause6ref) from claim where cause6ref=50)*100)/ @ClaimCount)'Others'
	--Non
	,(((select count(cause6ref) from claim where cause6ref=0)*100)/ @ClaimCount)'Non'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-03 : 06:14:40

multiply by 100.0 instead of 100

bombdrop
Starting Member

5 Posts

Posted - 2006-10-03 : 06:27:55

Thank you v.much

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 06:44:22

All the 14 subqueries must kill the performance. Try this query and compare speed

SELECT	1.0 * COUNT(*) 'Total Claims',
	1.0 * SUM(CASE WHEN cause6ref = 1 THEN 1 ELSE 0 END) 'Appointment Stage',
	1.0 * SUM(CASE WHEN cause6ref = 2 THEN 1 ELSE 0 END) 'During Site Inverstigation/Survey',
	1.0 * SUM(CASE WHEN cause6ref = 3 THEN 1 ELSE 0 END) 'Design Stage',
	1.0 * SUM(CASE WHEN cause6ref = 4 THEN 1 ELSE 0 END) 'During Construction',
	1.0 * SUM(CASE WHEN cause6ref = 50 THEN 1 ELSE 0 END) 'Others',
	1.0 * SUM(CASE WHEN cause6ref = 0 THEN 1 ELSE 0 END) 'Non'
FROM	Claim
UNION ALL
SELECT	100.0 'Total Claims',
	100.0 * SUM(CASE WHEN cause6ref = 1 THEN 1 ELSE 0 END) / COUNT(*) 'Appointment Stage',
	100.0 * SUM(CASE WHEN cause6ref = 2 THEN 1 ELSE 0 END) / COUNT(*) 'During Site Inverstigation/Survey',
	100.0 * SUM(CASE WHEN cause6ref = 3 THEN 1 ELSE 0 END) / COUNT(*) 'Design Stage',
	100.0 * SUM(CASE WHEN cause6ref = 4 THEN 1 ELSE 0 END) / COUNT(*) 'During Construction',
	100.0 * SUM(CASE WHEN cause6ref = 50 THEN 1 ELSE 0 END) / COUNT(*) 'Others',
	100.0 * SUM(CASE WHEN cause6ref = 0 THEN 1 ELSE 0 END) / COUNT(*) 'Non'
FROM	Claim

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-03 : 08:45:19

For more informations read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-03 : 09:26:40

How about this :


SELECT	a * COUNT(*) 'Total Claims',
	a * SUM(CASE WHEN cause6ref =  1 THEN 1 ELSE 0 END) 'Appointment Stage',
	a * SUM(CASE WHEN cause6ref =  2 THEN 1 ELSE 0 END) 'During Site Inverstigation/Survey',
	a * SUM(CASE WHEN cause6ref =  3 THEN 1 ELSE 0 END) 'Design Stage',
	a * SUM(CASE WHEN cause6ref =  4 THEN 1 ELSE 0 END) 'During Construction',
	a * SUM(CASE WHEN cause6ref = 50 THEN 1 ELSE 0 END) 'Others',
	a * SUM(CASE WHEN cause6ref =  0 THEN 1 ELSE 0 END) 'Non'
FROM	Claim cross join 
        (select a = 1.0 union all select 100.0) b
group by a

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 09:30:39

No.
What about the percentage when multiplying with 100?
Maybe, if you change

FROM	Claim cross join 
        (select a = 1.0 union all select 100.0) b

FROM	Claim cross join 
        (select a = 1.0 union all select 100.0 / (select count(*) from claim)) b

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-03 : 10:04:27

quote:
Originally posted by Peso

No.
What about the percentage when multiplying with 100?
Maybe, if you change
FROM Claim cross join
(select a = 1.0 union all select 100.0) b
to
FROM Claim cross join
(select a = 1.0 union all select 100.0 / (select count(*) from claim)) b

Peter Larsson
Helsingborg, Sweden

missed that part

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources