SQL Divide by Two Count()

Question

I have the following query, which is trying to figure out the percentage of a certain product compared to the total number of products. IE: [Product Count] / [Total Products] = Percent

;WITH totalCount AS(
    SELECT 
        CAST(COUNT(id) as Integer)as totalCount
    FROM TABLE_NAME
)
SELECT 
    ((CAST(COUNT(DISTINCT id) as Integer)/(SELECT * FROM totalCount))*100) as 'Percent'
FROM TABLE_NAME

However, the percent column always returns "0" unless there is only one record. In addition, is there a way to add the totalCount and Select query into one?

Basically, how do you divide two Count() fields?

Solution 1

Cast your total count as a number besides integer (DECIMAL?) - the math rounds off.

Solution 2

Cast as something with decimal precision, not Integer. A float or real.

select cast(distinctCount as real)/cast(totalCount as real) * 100.00
   , distinctCount
   , totalCount
from (
 select count(distinct id) as distinctCount
  , count(id) as totalCount
  from Table) as aggregatedTable

Solution 3

Shouldn't that be:

;WITH totalCount AS(
    SELECT 
        CAST(COUNT(id) as Integer)as totalCount
    FROM TABLE_NAME
)
SELECT 
    ((CAST(COUNT(DISTINCT id) as Integer)*100/(SELECT count(*) FROM totalCount))) as 'Percent'
FROM TABLE_NAME

Note the SELECT COUNT(*). Also, you should multiply before you divide, otherwise you'll always get zero

Comments

[Jefe]

I don't think so, as the totalCount query only returns one row, so if you add in the COUNT(*) it will return 1 always, instead of the actual total. Thanks for the help though :)

[Jefe]

Thanks, that worked! It's always the simple things that get you.

[Philippe Leybaert]

okay :-) But multiplying by 100 will certainly solve your problem.

[Matthew Vines]

Make sure you comment that code. The next developer along may not be as clever as you were.

[dburges]

use decimal never use float or real in a math calculation as they are inexact datatypes and can introduce rounding errors.

[Joel Mansford]

I usually use an even simpler hack and instead of multiplying by 100 multiply by 100.0