Percentage Values with one decimal place

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)

Percentage Values with one decimal place

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-15 : 14:59:19

Hi,

I am using SQL 2005.

I would like to get the following calculated values with one decimal place.
% value of TradeCount/InquiryCount.
When I do as below I get a value of
63 for HitRatioCount

SELECT
HitRatioCount = TradeCount*100/InquiryCount ,
HitratioVolume = TradeVolume*100/InquiryVolume --format to one decimal place
INTO #HitRatio
FROM #DealerTemp

example TradeCount = 14
InquiryCount = 22.

When I do as below

SELECT
HitRatioCount = TradeCount/InquiryCount * 100 ,
HitratioVolume = TradeVolume/InquiryVolume * 100 --format to one decimal place
INTO #HitRatio
FROM #DealerTemp

I get 0 for HitRatioCount

Which is the correct way and how do i specify one decimal place?

Thanks

Sachin.Nand

2937 Posts

Posted - 2010-03-15 : 15:12:16

SELECT
HitRatioCount = TradeCount/InquiryCount * 100*1.0 ,
HitratioVolume = TradeVolume/InquiryVolume * 100*1.0--format to one decimal place
INTO #HitRatio
FROM #DealerTemp

PBUH

Ehan
Starting Member

19 Posts

Posted - 2010-03-15 : 15:16:16

declare @TradeCount int , @InquiryCount int
set @TradeCount = 14
set @InquiryCount = 22

SELECT
HitRatioCount = CAST(CAST(@TradeCount as decimal) /@InquiryCount * 100 AS decimal(3,1))

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-15 : 15:29:07

Thanks you so much, that worked. I was trying the CONVERT function with decimal and that gave me an incorrect result set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 00:33:49

quote:
Originally posted by Idera

SELECT
HitRatioCount = TradeCount/InquiryCount * 100*1.0 ,
HitratioVolume = TradeVolume/InquiryVolume * 100*1.0--format to one decimal place
INTO #HitRatio
FROM #DealerTemp

PBUH

its same as 100.0

so this will suffice
TradeCount/InquiryCount * 100.0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-16 : 10:39:40

Thanks.

When I implemented the following :

SELECT
--HitRatioCount = CONVERT(decimal(3,3),ROUND(4,1,TradeCount/InquiryCount)),
HitRatioCount = CAST(CAST(TradeCount as decimal) /InquiryCount * 100 AS decimal(3,1)),
HitRatioVolume = CAST(CAST(TradeVolume as decimal) /InquiryVolume * 100 AS decimal(3,1)) --format to one decimal place
INTO #HitRatio
FROM #DealerTemp

I get the results fine.

However, when I am joining to various work/temp tables I get the below error for Arithmatic Overflow. I am concerned about the final select when I join to the table that has the HitRatio.

SELECT
Product = prd.Description,
SizeOrder = CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
SizeBucket = CASE
WHEN iql.USDSize <= 1000 THEN '<=1MM'
WHEN iql.USDSize > 1000 THEN '>1MM'
END,
--DealerBucket = CASE
--WHEN rsc.DealerCnt <= 10 THEN '<=10 Dealers'
--WHEN rsc.DealerCnt > 10 THEN '>10 Dealers'
--END,
-- ResponseCount = rsc.ResponseCnt,
InquiryCount = COUNT(DISTINCT inq.MISInquiryID),
InquiryVolume = SUM(iql.USDSize),
TradeCount = COUNT(DISTINCT trd.MISInquiryID),
TradeVolume = SUM(IsNull(trd.USDSize,0)),
--AvgDlrCnt = AVG(convert(float, rsc.DealerCnt)),
--AvgRspCntWithLevel = AVG(convert(float, rsc.ResponseCnt)),
ProductID = iql.ProductID
-- HitRatioCount = TradeCount/InquiryCount
-- HitRatioVolume = TradeVolume/InquiryVolume

INTO
#DealerTemp
FROM
Inquiry inq
INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID
--INNER JOIN
--#ResponseCnt rsc
--ON
--inq.MISInquiryID = rsc.MISInquiryID
LEFT JOIN
Trade trd
ON
trd.MISInquiryID = iql.MISInquiryID
and trd.LegSequence = iql.LegSequence
and trd.IsVolume = 1
INNER JOIN
Product prd
ON
prd.ProductID = iql.ProductID
WHERE
iql.IsVolume = 1
and inq.IsVolume = 1
and inq.InquiryDate BETWEEN @StartDate and @EndOfEndDate
and inq.InquiryType = 'F'
and iql.ProductID in (1,2,6,7,4,40)
GROUP BY
CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
CASE
WHEN iql.USDSize <= 1000 THEN '<=1MM'
WHEN iql.USDSize > 1000 THEN '>1MM'
END,
iql.ProductID,
prd.Description

select * from #DealerTemp
SELECT
--HitRatioCount = CONVERT(decimal(3,3),ROUND(4,1,TradeCount/InquiryCount)),
HitRatioCount = CAST(CAST(TradeCount as decimal) /InquiryCount * 100 AS decimal(3,1)),
HitRatioVolume = CAST(CAST(TradeVolume as decimal) /InquiryVolume * 100 AS decimal(3,1)) --format to one decimal place
INTO #HitRatio
FROM #DealerTemp
select * from #HitRatio
drop table #HitRatio
--CASE
--WHEN rsc.DealerCnt <= 10 THEN '<=10 Dealers'
--WHEN rsc.DealerCnt > 10 THEN '>10 Dealers'
--END

SELECT
Product,
SizeBucket,
--DealerBucket,
InquiryCount,
InquiryVolume,
TradeCount,
TradeVolume,
--AvgDlrCnt,
--AvgRspCntWithLevel
-- SizeOrder,
-- ProductID
from
#DealerTemp
order by
SizeOrder,
ProductID,
--DealerBucket

Warning: Null value is eliminated by an aggregate or other SET operation.

(66403 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.

(210 row(s) affected)
Msg 8115, Level 16, State 8, Line 135
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

(210 row(s) affected)

Should I try the ISNULL. Not sure what is causing an overflow.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-16 : 10:54:29

I tried replacing the CAST with the below but my data looks weird.

HitRatioCount = TradeCount/InquiryCount * 100.0,
HitRatioVolume = TradeVolume/InquiryVolume * 100.0

Result:
Prd HitCount HitVol
1 0.0 0.00000
1 0.0 74.57000
1 0.0 65.61000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:23:24

what do you mean by weird? can you explain what you're getting & what you expect?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-16 : 11:30:21

I expect something like this:

HitRatio HitRatio
Count Volume

73.3 72.5
57.9 60.7
37.1 33.7

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:32:29

on what basis? what are values for source fields to get above output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-16 : 11:38:08

example 61256/83561 should give 73.3 %
602/1040 should give 57.9 %

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:40:23

does this make any difference?

HitRatioCount = TradeCount* 100.0/InquiryCount ,
HitRatioVolume = TradeVolume* 100.0/InquiryVolume

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-16 : 12:28:06

I get results as below: I need to format values to decimal(3,1)? I tried
CONVERT(FLOAT(TradeCount* 100.0/InquiryCount),3,1) but it gives an error.

0.000000000000 0.000000000000000
42.882882882882 39.188550968181269
65.009940357852 63.541363592280089
69.940476190476 69.137830238579104

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 12:31:10

HitRatioCount = cast(TradeCount* 100.0/InquiryCount as decimal(3,1)),
HitRatioVolume = cast(TradeVolume* 100.0/InquiryVolume as decimal(3,1))
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-16 : 12:35:29

I get the arithmatic overflow

Msg 8115, Level 16, State 8, Line 141
Arithmetic overflow error converting numeric to data type numeric.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-16 : 12:38:22

It works when I exec teh below query separatley

SELECT
--HitRatioCount = CONVERT(decimal(3,3),ROUND(4,1,TradeCount/InquiryCount)),
--HitRatioCount = CAST(CAST(TradeCount as decimal) /InquiryCount * 100 AS decimal(3,1)),
--HitRatioVolume = CAST(CAST(TradeVolume as decimal) /InquiryVolume * 100 AS decimal(3,1)) --format to one decimal place
--HitRatioCount = (TradeCount/InquiryCount )* 100.0,
--HitRatioVolume = (TradeVolume/InquiryVolume) * 100.0
HitRatioCount = cast(TradeCount* 100.0/InquiryCount as decimal(3,1)),
HitRatioVolume = cast(TradeVolume* 100.0/InquiryVolume as decimal(3,1))

INTO #HitRatio
FROM #DealerTemp
select * from #HitRatio

Hit Hit
Ratio Volume
Count Count
73.3 72.5
57.9 60.7
37.1 33.7
67.6 73.9
63.6 58.6
50.0 48.6
72.2 70.3
71.7 76.8
29.9 27.5
76.3 71.5
78.9 69.2
54.7 51.1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 12:38:36

HitRatioCount = cast(TradeCount* 100.0/InquiryCount as decimal(10,1)),
HitRatioVolume = cast(TradeVolume* 100.0/InquiryVolume as decimal(10,1))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-03-16 : 13:12:17

This worked now. Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 13:13:13

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources