Divide SUM() by COUNT() in select statement-CodePudding

I'm running into an issue

SELECT 
  test_assignment,
  count(distinct item_id) as num_items,
  SUM(view_binary) as total_views,
  SUM(view_binary_30) as binary_30_day

and produces a table like this

test_assignment num_items tot_views binary_30_day
0                1130      110247   1913
1                1068      104102   1860

I'm trying to add in a line that thats the binary_30_day sum, and divides it by the item count (so 1913/1130, 1860/1068)

I've tried (SUM(view_binary_30)/COUNT(DISTINCT item_id)) but it always returns 1 for both

What am I doing wrong?

CodePudding user response：

AS @serg mentioned above, Please try converting either of Numerator or Denominator to decimal by multiplying with 1.0

SELECT
  test_assignment,
  COUNT(DISTINCT item_id) AS num_items,
  SUM(view_binary) AS total_views,
  SUM(view_binary_30) AS binary_30_day,
  SUM(view_binary_30) * 1.0 / COUNT(DISTINCT item_id) AS XYZ

Also if you encounter a divide by 0 error, then try converting the denominator to

CASE 
  WHEN COUNT(DISTINCT item_id) = 0 
    THEN NULL 
  ELSE COUNT(DISTINCT item_id) 
  END

If you are writing your script in bigquery then, you could simply use

SAFE_DIVIDE(SUM(view_binary_30) * 1.0, COUNT(DISTINCT item_id)) AS XYZ

CodePudding user response：

The issue is the calculated don't exist until the select statement returns. You can use a sub query though and return your statistics.

SELECT  sub.test_assignment, sub.num_items, sub.total_views, sub.binary_30_day, 

(Cast(sub.binary_30_day as float) / sub.num_items) as rate
FROM (
SELECT 
  test_assignment,
  count(distinct item_id) as num_items,
  SUM(view_binary) as total_views,
  SUM(view_binary_30) as binary_30_day
) sub

Might wont to add a case when to deal with dividing by zero.