-1

I'm trying to get the percentage of times something occurs (a task requires an outbound call)

So I made a count distinct of the occurrence "CALLED THE CUSTOMER" and aliased it as outbound_calls. And counted all the tasks alias "total_tasks"

These two steps work and I get this

enter image description here

Now I'm trying to divide outbound_calls by total_taks to get the percentage but get an error saying that the name oubound_calls is not recognized

This is the query I'm writing


SELECT
COUNT(DISTINCT IF(Out_CONTACT_TYPE = 'CALLED THE CUSTOMER', booking_id, NULL )) AS outbound_calls,
COUNT(task_id) AS total_tasks,
DIV(outbound_calls, total_tasks)
FROM `cs-reporting...`

Any ideas?

Happy Friday :)

AngelCel
  • 13
  • 3

1 Answers1

0

I hope this can help.

You can use a subquery in this case :

SELECT 
   outbound_calls,
   total_tasks,
   DIV(outbound_calls, total_tasks)
FROM
(
  SELECT
   COUNT(DISTINCT IF(Out_CONTACT_TYPE = 'CALLED THE CUSTOMER', booking_id, NULL )) AS outbound_calls,
   COUNT(task_id) AS total_tasks
  FROM `cs-reporting...`
)

You can also use a WITH bloc :

WITH reporting AS
(
    SELECT
     COUNT(DISTINCT IF(Out_CONTACT_TYPE = 'CALLED THE CUSTOMER', booking_id, NULL )) AS outbound_calls,
     COUNT(task_id) AS total_tasks
    FROM `cs-reporting...`
)

SELECT 
   outbound_calls,
   total_tasks,
   DIV(outbound_calls, total_tasks)
FROM reporting;

For divisions with BigQuery, you can use SAFE_DIVIDE with ROUND functions (example with 2 digits) :

SELECT 
  outbound_calls,
  total_tasks,
  ROUND(SAFE_DIVIDE(outbound_calls, total_tasks), 2) as divres
FROM
(
SELECT 
   5 as outbound_calls,
   3 as total_tasks
);

According to the BigQuery documentation the aliases are visibles in the from not in the select bloc directly.

Mazlum Tosun
  • 5,761
  • 1
  • 9
  • 23
  • Thanks @Mazlum. Now the "plot thickens" the result of the division is 0 even if I cast it as a float I get 0.0 when I know it should be around 0.48. Any suggestions – AngelCel Nov 04 '22 at 10:21
  • You're welcome, can you give me an example of values for `outbound_calls` and `total_tasks`, and I give it a try :) – Mazlum Tosun Nov 04 '22 at 10:24
  • 1
    I updated my answer to give you an example of division with `BigQuery`. May you add an upvote to make my answer more valuable please ? – Mazlum Tosun Nov 04 '22 at 10:32
  • Hey, you are so kind :) I have 127222 outbound_calls and 263729 total_tasks – AngelCel Nov 04 '22 at 11:07