1

I've got a table that stores survey question information and I'm looking to find out a count based on gender, race, etc My issue is that I have to reference the same columns multiple times in the where clause and I'm not sure the best way to do that.

example:

I need to know the count all of the people that answered yes to survey question 24 AND ALSO yes to questions 4 or 16 or 24. I have tried messing with Or statements and IN statements but both aren't working for me. I was looking at using a case statement in the WHERE clause but not sure what that would need to look like. Any help or advice would be most appreciated!!

  with TheTotal as
(select count(table.name_id)over (partition by
table.other_id) as blah
where sex='M'
 AND table.date between '2019-01-01' and '2019-09-30'
 AND (table.jq_nbr=35 AND table.answer='Y')

 AND (
  (jq_nbr=4 and answer='Y')
  OR (jq_nbr=16 and answer='Y')
  OR (jq_nbr=24 and answer='Y')
      )
  • can you post the schema of the table? – kevinnwhat Oct 11 '19 at 19:43
4

Having your schema would be helpful in helping with your specific query. Without the full schema, here are some pointers for how you might write a query to get your results.

Assuming you have a single dbo.SurveyResults table, where there is some ID (survey_id) that is used to identify all the results from a given user's responses, you could use logic that is something like this:

  • Count all the "Question 35"s that have an answer of Y, and there also exists an answer for questions 4, 16, or 24 that also has an answer of Y.

That query would look something like this:

SELECT COUNT(DISTINCT r.survey_id)
FROM dbo.SurveyResults AS r
WHERE r.jq_nbr = 35
AND r.answer = 'Y'
AND EXISTS (SELECT 1 
            FROM dbo.SurveyResults AS r2
            WHERE r2.survey_id = r.survey_id
            AND r2.jq_nbr IN (4,16,24)
            AND r2.answer = 'Y'
            );

With a slightly different schema, perhaps there is a "header" table (dbo.Survey) that has has one row per user-completed survey, and another table (dbo.SurveyResults) that is a child table that has one row per question answered. With this, you can use a slightly different "plain English" description, which might generate slightly different logic:

  • Count all the user-completed surveys where the respondent has answered "Y" to question 35, and also answered "Y" to at least on of questions 4,16, or 24.

That query might look like this:

SELECT COUNT(s.survey_id)
FROM dbo.Survey AS s
WHERE EXISTS (SELECT 1 
            FROM dbo.SurveyResults AS r1
            WHERE r1.survey_id = s.survey_id
            AND r1.jq_nbr = 32
            AND r1.answer = 'Y'
            )
AND EXISTS (SELECT 1 
            FROM dbo.SurveyResults AS r2
            WHERE r2.survey_id = s.survey_id
            AND r2.jq_nbr IN (4,16,24)
            AND r2.answer = 'Y'
            );
| improve this answer | |
  • This is exactly what I needed; thank you very much! Using the Where Exists is the way to go! – Muab Nhoj Oct 14 '19 at 19:12
1

You can nest your CASE statements, as follows:

CASE WHEN <condition 1> THEN
            CASE WHEN <sub-condition 1> THEN <output 1> ELSE <output 2> END
    WHEN <condition 2> THEN
            CASE WHEN <sub-condition 2> THEN <output 3> ELSE <output 4> END
    ELSE <output 5> END AS CaseTest

You start each statement with CASE and terminate it with END, with the option of adding AS <alias> if it is the outermost or only CASE in a SELECT and you'd like a column name associated with it.

| improve this answer | |
  • Nesting the where clauses did work but it took several minutes to run by the time I added the logic to it. Thank you for showing me this though because I definitely learned more about the CASE statement. – Muab Nhoj Oct 14 '19 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.