SQL – How to get the percentage of 2 sums

I am trying to get the CTR from uploads to clicks. How would I be able to get this percentage?

I am currently using the code below and I receive 0 as a result (view attached img).

SELECT  geo_country, [created_at:date:aggregation] AS day,
  SUM(case when name = 'adclick' then 1 else 0 end) as clicks,
  SUM(case when name = 'camera_upload_image' then 1 else 0 end) as uploads,
  CONVERT(DECIMAL(10,2), clicks / NULLIF(uploads, 0)) As CTR
FROM events

​x
 
SELECT  geo_country, [created_at:date:aggregation] AS day,  SUM(case when name = 'adclick' then 1 else 0 end) as clicks,  SUM(case when name = 'camera_upload_image' then 1 else 0 end) as uploads,  CONVERT(DECIMAL(10,2), clicks / NULLIF(uploads, 0)) As CTRFROM events​

Results:

Answer

What you’re running into is integer division.

If you take 2 integers

clicks = 3 
uploads = 2

 
clicks = 3 uploads = 2​

Then clicks / uploads equals 1 – not 1.5 as you expect:

http://sqlfiddle.com/#!18/0ed4a9/8/0

As you can see from that fiddle, the way around this is to ensure that your values are cast/converted to floating point numbers before doing the division.

Advertisement

Answer