Divide by question

Question

Post reply

Divide by question

SQL33

SSCrazy

Points: 2044
More actions
March 8, 2007 at 10:41 am

#123142

I need to do a calculation to tell me the percent of items by dividing the count (total number) of items by a given predetermined number input at runtime .. example .. divide it by 110.

Example:

SELECT number, COUNT(DATE) AS Days

FROM Table

GROUP BY number

But .. when I try to apply the help as shown in Books online,

SELECT ((count(DATE)))/110 AS 'Royalty Amount'

FROM table

GROUP by number

I get the value "0" in the result of the division.

Is this because I need to convert the result of the ((count(date)))/ part to an integer prior to doing the "/110" part?

Thanks~!

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Converting it to an integer will not get you what you want. The result of COUNT() is an integer. Since integers are whole numbers, dividing integers by integers will generate a whole number result, 0 in your case. You will need to CAST/CONVERT one of your values to a decimal or use 110.00 in place of the integer.

DECLARE @table TABLE (DateCol datetime)

INSERT INTO @table

SELECT GETDATE() UNION ALL

SELECT GETDATE()

SELECT COUNT(DateCol) / 110

FROM @table

SELECT CAST(COUNT(DateCol) AS dec) / 110

FROM @table

SELECT COUNT(DateCol) / 110.00

FROM @table

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

SQL33 SSCrazy Points: 2044 More actions · Answer 2

SQL33

SSCrazy

Points: 2044

March 9, 2007 at 8:01 am

#694105

Thanks.. that worked great. Is it possible to display the % sign in the calculated result and limit the decimal places to less than the 6 shown by default?

Thanks

Steve Fine Ten Centuries Points: 1134 More actions · Answer 3

Steve Fine

Ten Centuries

Points: 1134

March 9, 2007 at 8:08 am

#694110

One way, there may be a simpler way.

SELECT cast(convert(decimal(8,1),100.0 *(COUNT(DateCol)/110.0))as Varchar)+'%'

FROM @table

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Another way... with right justification to boot...

SELECT STR(COUNT(DateCol)/110.0*100.0,8,1)+'%'

FROM @Table

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions