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
"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;