CODEWITHSUNDEEP
sqlsql-servermath
user2697262
user2697262

Reputation: 11

Math in SQL query not working

I have a sql query (SQL server 2005) that's creating a var and doing some math. The math works when the ticket count is 0 or 250000, but it's not creating a decimal point when the ticket count is any other value. (It reads 0.) Here is the query -

SELECT ticketCount, ((250000 - ticketCount) / 250000) * 100 AS percentSold 
FROM raffleTickets

Where ticketCount in the DB is how many tickets of 250000 remain to be sold. If ticketCount is 250000, percentsold is 0, which is correct. If ticketCount is 0, percentSold is 100, which is correct. For all other values, percentSold is returning 0.

Please help! Thanks.

Upvotes: 0

Views: 1197

Answers (3)

user3648999
user3648999

Reputation: 31

The only thing you need to do is putting plus or minus 0.0 in your formula.

SELECT ticketCount, ((250000 - ticketCount - 0.0) / 250000) * 100 AS percentSold 
FROM raffleTickets

Upvotes: 0

ChuckCottrill
ChuckCottrill

Reputation: 4444

Your formula is correct,

PercentSold = (TotalTickets - TicketsRemaining) / TotalTickets

But what is the Domain and Range of the above function? Your numbers are all expressed as integer, but you probably want to calculate using Real.

PercentSold = ( ( TotalTickets - TicketsRemaining )*100.0) / (TotalTickets)

This forces the calculation to be done in Real. Languages that know how to declare variables and calculations in specific types (i.e. Domains) can make this clearer, more apparent.

Wanting to represent a real number with a certain precision means you want to perform output formatting. SqlServer must have a way to specify output format for numbers in the Real Domain. I defer to SqlZoo.net for their answer to formatting.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

SQL Server does integer division (this varies among databases).

You can easily fix this by putting a decimal point after the constants:

SELECT ticketCount, ((250000.0 - ticketCount) / 250000.0) * 100.0 AS percentSold
FROM raffleTickets;

If you want the integer portion, then you can cast() the result back to an integer. Alternatively, you can use the str() function to convert the value of percentSold to a string with the appropriate number of decimal points.

Upvotes: 1

Related Questions