CODEWITHSUNDEEP
sqlsql-server
user222427
user222427

Reputation:

'select count' when divided by another select count only returns when 1=1

I'm basically trying to get the percent of two select counts. In the example shown below, I should have a simple 2 / 6 which results in 33.33%. However when I run the query is only returns either 100% or 0%. Any help on why it does what it does would be fantastic

CONVERT(DECIMAL(4, 2), 
(SELECT Count(driver)
 FROM trh a
 WHERE a.hn = trh.hn
   AND a.driver = trh.driver
   AND (a.finishposition < 5
        AND a.finishposition IS NOT NULL
        AND a.finishposition != 0 )) / Count(driver) ) * 100

Now that I try CONVERT(DECIMAL(4, 2), 2 / 6), this just returns 0.00 also

Okay you have to cast them as decimals

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

SQL Server does integer division. Just cast() one of the values to a decimal or floating type. For instance:

/ cast(Count(driver) as decimal(4, 2))

Upvotes: 1

Related Questions