SQL Having condition, when checking 2 columns

July 22, 2024

In general, I have a working query which is "Having A or B". It works, but I also want to add one more statement. I tried "Having (A and C) or (B and C)" -> gives no user_id, tried "Having (A or B) and C" -> no result.

Here is the working "A or B" query (gets 21796, 21797 user_id’s):

SELECT user_survey.user_id 
FROM user_survey
WHERE survey_id IN (2, 8, 12) OR survey_id IN (2, 8, 13)
GROUP BY user_survey.user_id
HAVING (COUNT(DISTINCT CASE WHEN survey_id IN (2, 8, 12) THEN survey_id END) = 3)
    OR (COUNT(DISTINCT CASE WHEN survey_id IN (2, 8, 13) THEN survey_id END) = 3);

Pivot table is having some of survey_id with values in value column.

Here what I want to check ("C"):

AND SUM(CASE WHEN survey_id = 18 AND CAST(value AS NUMERIC) > 26 THEN 1 ELSE 0 END) > 0

so if there is row where survey_id = 18 and value > 26, then take that user_id.

When I add this "C" condition I want to get only 21797 user_id’s, but get empty result.

id;user_id;survey_id;value;valn
5111;21796;2;\N;\N
5112;21796;8;\N;\N
5113;21796;13;\N;\N
5114;21796;18;18;\N
5119;21797;2;\N;\N
5120;21797;8;\N;\N
5121;21797;12;\N;\N
5122;21797;18;33;\N

Please help

>Solution :

"Having (A and C) or (B and C)" can be simplified to "Having (A OR B) AND C"

Looks like you need

SELECT
  us.user_id
FROM user_survey us
WHERE us.survey_id IN (2, 8, 12, 13, 18)
GROUP BY
  us.user_id
HAVING (
    COUNT(CASE WHEN us.survey_id IN (2, 8, 12) THEN 1 END) = 3
    OR
    COUNT(CASE WHEN us.survey_id IN (2, 8, 13) THEN 1 END) = 3
)
AND COUNT(CASE WHEN us.survey_id = 18 AND TRY_CAST(us.value AS NUMERIC) > 26 THEN 1 END) > 0;

Note the use of TRY_CAST to prevent errors.

If you really do need to distinct over survey_id then do that first in a subquery/derived table, as it’s more efficient.

SELECT
  us.user_id
FROM (
    SELECT
      us.user_id,
      us.survey_id,
      COUNT(CASE WHEN us.survey_id = 18 AND TRY_CAST(us.value AS NUMERIC) > 26 THEN 1 END) AS caseC
    FROM user_survey us
    WHERE us.survey_id IN (2, 8, 12, 13, 18)
    GROUP BY
      us.user_id,
      us.survey_id
) us
GROUP BY
  us.user_id
HAVING (
    COUNT(CASE WHEN us.survey_id IN (2, 8, 12) THEN 1 END) = 3
    OR
    COUNT(CASE WHEN us.survey_id IN (2, 8, 13) THEN 1 END) = 3
)
AND SUM(us.caseC) > 0;