Problems calculating percentage - help with query

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

You need to cast the COUNT to DECIMAL/NUMERIC ;

try this:

SELECT ColumnA, COUNT(ColumnB) AS TotalApps ,

(100.00 * (cast(COUNT(ColumnB) as numeric(5,2)) / (select COUNT(*) FROM @table) )) AS PercentofTotal

FROM @table

GROUP BY ColumnA

ORDER BY TotalApps, PercentofTotal ASC

dave.molyneaux SSC Veteran Points: 283 More actions · Answer 2

I'm now getting an error 8115, Arithmetic overflow converting int to data type numeric

Sir Hossfly Say Hey Kid Points: 663 More actions · Answer 3

You can grab a percentage this way...

select (select cast(COUNT(ColumnB) as decimal) / (SELECT COUNT(*) FROM Table)

FROM from table) * 100 as PercentofTotal

SQLMD Right there with Babe Points: 764 More actions · Answer 4

If you're trying to create a result set from a single select then my suggestion won't work, but you could use a variable to achieve this:

declare @countOfAll decimal(5, 2)

select @countOfAll = COUNT(*) from Table

SELECT ColumnA, COUNT(ColumnB) as TotalApps,

COUNT(ColumnB) / @countofAll * 100 AS PercentofTotal

FROM Table

GROUP BY ColumnA

ORDER BY TotalApps, PercentofTotal ASC

GO

This has the added bonus of returning the answer in decimal format without casting since the variable is declared as decimal.