Sql-server – reference the same two columns multiple times in where clause

sql servert-sql

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')
      )

Best Answer

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'
            );
Related Question