SQL Server Create a percentage formula with using a case when expression

j8yoct9x 于 12个月前发布在其他

关注(0)|答案(2)|浏览(94)

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%

sql-server

来源：https://stackoverflow.com/questions/77622815/create-a-percentage-formula-with-using-a-case-when-expression

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...

select
sum(t.row_calcs) proportion_who_answered_question_one
from
(
 select
 100.0*case when fq.question_one_answer is not null then 1 end
  /count(*) over() row_calcs
 from customer_visit cv
 join visittype v
 on cv.visittype_id=v.visittype_id
 and v.visittype='form questions'
 join form_questions fq
 on cv.customervisit_id=fq.customervisit_id
) t;

赞(0）回复(0）举报 12个月前

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.

SELECT
   CONCAT(COUNT(fq.question_one_answer), '/', COUNT(*)) AS fraction,
   COUNT(fq.question_one_answer) * 100.0 / COUNT(*) AS percentage
FROM dbo.customervisit cv
JOIN dbo.form_questions fq ON fq.customervisit_id = cv.customervisit_id
WHERE cv.visittype_id = 
        (SELECT visittype_id FROM dbo.visittype WHERE visittype = 'Form Questions');

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Create a percentage formula with using a case when expression

2条答案

相关问题

热门标签

最新问答