Grouping results based on CASE statement?-CodePudding

I'm working with a table that stores the results of a questionnaire administered to people. Each question and its result is stored as a separate record, as shown below. I've written a CASE statement that creates a simple 1/0 flag depending on people's answers to certain questions. My results look something like this.

PersonID    Question       Answer       Flag
---------------------------------------------
1001        Question 1     yes          1
1001        Question 2     3            0
1001        Question 3     1 or more    1
1234        Question 1     no           0
1234        Question 2     2            0
1234        Question 3     none         0

My issue now is that if a person has even one flagged response, I need to flag their entire questionnaire. I've been looking around for other examples of this issue—this is dealing with almost exactly the same thing, but I don't necessarily want to actually group my results, because I still want to be able to see the flags for the individual questions ("oh, this person's questionnaire got flagged, let's see which question it was for and what their response was"). I know it's probably not the most efficient thing, but I'm hoping for results that look like this:

PersonID    Question       Answer       Flag    Overall
--------------------------------------------------------
1001        Question 1     yes          1         1
1001        Question 2     3            0         1
1001        Question 3     1 or more    1         1
1234        Question 1     no           0         0
1234        Question 2     2            0         0
1234        Question 3     none         0         0

This is where I'm at with my query. It works fine for flagging the individual questions, but I'm not sure what steps to take in order to flag the whole questionnaire based on the individual answers. What kind of logic/syntax should I be looking at?

SELECT
    PersonID,
    QuestionDescription as Question,
    ResultValue as Answer,
    (CASE
        WHEN (QuestionDescription LIKE '%ion 1%' AND ResultValue = 'yes') THEN 1
        WHEN (QuestionDescription LIKE '%ion 2%' AND ResultValue >= 5) THEN 1
        WHEN (QuestionDescription LIKE '%ion 3%' AND ResultValue = '1 or more') THEN 1
        ELSE 0
     END) as Flag

FROM Questionnaire

ORDER BY PersonID, QuestionDescription

CodePudding user response：

At its most simplistic you can add up the flags within a partition of the person and see whether they sum to 0 or not:

WITH x AS
(
  SELECT
    PersonID,
    QuestionDescription as Question,
    ResultValue as Answer,
    CASE
        WHEN (QuestionDescription LIKE '%ion 1%' AND ResultValue = 'yes') THEN 1
        WHEN (QuestionDescription LIKE '%ion 2%' AND ResultValue >= 5) THEN 1
        WHEN (QuestionDescription LIKE '%ion 3%' AND ResultValue = '1 or more') THEN 1
        ELSE 0
     END as Flag
  FROM Questionnaire
)

SELECT 
  *,
  CASE WHEN SUM(Flag) OVER(PARTITION BY PersonID) > 0 THEN 1 ELSE 0 END as Overall
FROM
  x

SUM(...) OVER(...) is a bit like doing the following:

WITH x AS ( 
  --your existing query here
)

SELECT *, CASE WHEN SumFlag > 0 THEN 1 ELSE 0 END as OVerall
FROM
  x
  INNER JOIN
  (SELECT PersonId, SUM(Flag) AS SumFlag FROM X GROUP BY PersonId) y ON x.PersonId = y.PersonId

i.e. SUM OVER does a grouping on PersonId, a Sum and then auto joins the result back to each row on the thing that was grouped (PersonId) - they're incredibly powerful and useful things, these window functions

This latter form (where a separate query groups and is rejoined) would also work if you can't get on with the window function (SUM OVER) approach - it's something akin to what datarocker pointed to in their answer

CodePudding user response：

Try using a CTE to capture a row per person that just computes the [Overall] column, then join that with your main query.