PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL Having condition, when checking 2 columns

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

Tags:

2

Answers


  1. 0

    "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;
    Login or Signup to reply.
  2. 0

    "Having (A or B) and C" -> no result

    That would be

    SELECT user_id 
FROM user_survey
WHERE survey_id IN (2, 8, 12, 13, 18)
GROUP BY 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
)
AND SUM(CASE WHEN survey_id = 18 AND CAST(value AS NUMERIC) > 26 THEN 1 ELSE 0 END) > 0;

    If this doesn’t return any result rows, then there is just no user_id that matches your conditions in the table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search