I am trying to create a formula for percentage while using a case when statement. I need numerator to acknowledge the amount of clients with a form_one_answer answer and the visit type being 'Form Questions'which is why I came up with this SQL formula:
(CASE WHEN "Customer"."customer_id" IS NOT NULL AND "Form_Questions"."question_one_answer" IS NOT NULL AND "Visittype"."visittype" = 'Form Questions' THEN 1 END) AS NUMER
I also need the dominator to acknowledge the total amount of clients with visit type being 'Form Questions' which I ended up making this SQL formula:
(CASE WHEN "Customer"."customer_id" IS NOT NULL AND ""Visittype"."visittype" = 'Form Questions' THEN 1 END) AS DENOM
What I am trying to do is get the total amount of clients who answered the question for question 1 and divide by the total amount of people who did the visit type 'form questions'. The question is listed in the 'form questions' visit but it is not required to be answered so not everybody who did the 'form questions' visit will answer that question. I did try to divide the numerator from the denominator then multiply it by 100 afterwards but it only leaves me with 100. I'm not sure why. This makes me wonder if I need the SUM() in here in order to consider the total amount of clients who answered the question for question 1 then divide by the total amount of people who did the visit type 'form questions'
This is how I did the SQL:
(((CASE WHEN "Customer"."customer_id" IS NOT NULL AND "Form_Questions"."question_one_answer" IS NOT NULL AND "Visittype"."visittype" = 'Form Questions' THEN 1 END)/(CASE WHEN "Customer"."customer_id" IS NOT NULL AND ""Visittype"."visittype" = 'Form Questions' THEN 1 END)) *100) AS PERCENTAGE
I did try converting the case when number into a float but I ran into errors with it. I did not see anything online where I could convert the 1 into a float while in the case when statement. I also did try using the SUM() for the numerator and denominator but ran into incorrect syntax errors and needing to use group by in order to use SUM(). I will say I am not sure if using the group by would be what I need to do in this situation. Last thing I tried was a subquery but I was having the same issues.
Here is my SQL:
SELECT distinct
"Form_Questions"."question_one_answer",
"Visittype"."visittype",
"CustomerVisit"."Visit_start_time",
"Customer"."age",
"Customer"."customer_id",
(CASE WHEN "Customer"."customer_id" IS NOT NULL AND "Form_Questions"."question_one_answer" IS NOT NULL AND "Visittype"."visittype" = 'Form Questions' THEN 1 END) AS NUMER,
(CASE WHEN "Customer"."customer_id" IS NOT NULL AND ""Visittype"."visittype" = 'Form Questions' THEN 1 END) AS DENOM
FROM "dbo"."Customer" AS "Customer"
INNER JOIN "dbo"."CustomerVisit" AS "CUSTOMERVISIT"
ON (
"Customer"."customer_id" = "CustomerVisit"."customer_id"
)
INNER JOIN "dbo"."Form_Questions"
ON (
CUSTOMERVISIT"."customervisit_id" = "Form_Questions"."customervisit_id"
)
INNER JOIN "dbo"."VisitType" AS "VISITTYPE"
ON (
"CUSTOMERVISIT"."visittype_id" = "VISITTYPE"."visittype_id"
)
WHERE "Visittype"."visittype" = 'Form Questions'
Here is some data to use for this:
customer_id | question_one_answer | visittype | age |
---|---|---|---|
00001 | yes | Form Questions | 6 |
00002 | no | Form Questions | 18 |
00003 | null | Form Questions | 17 |
The result I would like to get is where the there is a new column that is a fraction like what is being divided and then another new column with the percentage total. Something like this:
fraction | percentage |
---|---|
2/3 | 66% |
2条答案
按热度按时间0aydgbwb1#
Would it be fair to say that the query asks, "What proportion of customers who came to the form questions visit answered question one?". If so, then customer_visit is the driving table. From what can be gleaned from the SQL, I think it would be fair to assume that customervisit_id is the primary key of the customer_visit table. That relation has a foreign key, visittype_id, to the visittype table and can be filtered early in the joins. The form_questions table seems to have at least two fields: customervisit_id and question_one_answer. From the wording surrounding the code, the latter either has a value or is null. To my mind, and without seeing the data, only three tables are required to return the required result set (a scalar): customer_visit, visittype and form_questions. The code below assumes a customer can only have one form questions visit and that there are no orphaned visits...
bnlyeluc2#
If you select all customer visits of type 'Form Questions' then
COUNT(*)
will give you the total number of visits.question_one_answer
is NULL when question #1 was not answered in the visit, otherwise it is NOT NULL. So,COUNT(question_one_answer)
will give you the number of visits where question #1 was answered.