Creating 2 columns based on condition

Question

0.00/5 (No votes)

See more:

So, I have survey tale where someone is asked roughly 5 questions. 3 of those questions are the same questions by the options to their answers are different . here are the questions:
ID Question Answer
101005 what brands did you purchase the past 5 months Coca-Cola or Pepsi or vitamin
101005 what brands did you purchase the past 5 months Dr.Pepper
101005 what brands did you purchase the past 5 months Other cheaper brand
101005 what brands did you purchase the past 5 months Coca-Cola

The goal is to create two extra column that shows if someone choose coca cola they are an existing customers marked as a 1 and potential_customer will be 0, and the other way around. Unfortunately, the code that I have still assign this user a 1 at for his Dr.Pepper answer. What can I do ensure that this code still recognizes this users.

What I have tried:

SQL

select [ID],[question],[answer],
         
 Case when Sum(Case when [answer] like'%coca-cola%' then 1 else 0 end)>=1 then 1 else 0 end  existing_customer
,Case when Sum(Case when [answer] like'%coca-cola%' then 1 else 0 end)=0 then 1 else 0 end  potential_customer

group by id,question, answer

Posted 26-Feb-20 9:01am

learning_new

Updated 26-Feb-20 21:15pm

Add a Solution

Comments

Tomas Takac 27-Feb-20 3:07am

For me scanning for a specific string raises a red flag, it is weird to hard code it like this. My feeling is that this flags are attributes of the answer itself so if you have an entity survey and entity answer then joining and aggregating by some personid should give you your answer.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-02-26T21:16:00

If i understand you correctly... you need to change your query as follow:

SQL

select [ID],[question],[answer],
 COUNT(CASE WHEN [answer] LIKE'%coca-cola%' THEN 1 ELSE NULL END) existing_customer
,COUNT(CASE WHEN NOT [answer] LIKE'%coca-cola%' THEN 1 ELSE NULL END) potential_customer
group by id,question, answer

Why?

MSDN wrote:
COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group, and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group, and returns the number of unique, nonnull values.

Source: COUNT (Transact-SQL) - SQL Server | Microsoft Docs[^]