append subquery based on case- postgresql

Question

My table is crash

id	jurisdiction	drugs_inv	alcohol_inv	dr_sex	severity_id
1	NT	true	false	Male	3
2	NSW	false	false	Male	3
3	WA	true	true	Female	3
4	WA	true	true	Male	4

I want to have a subquery based on t_factors array

if it has 1 then filter where alcohol_inv = true
if it has 2 then filter where drugs_inv = true
if both 1 and 2 then use both above conditions

something like may be append a subquery...within the query in the following spaces..

[subqry for alcohol_inv = true]
[subqry for drugs_inv = true]

how to go about it?

My query:

WITH myvars(t_state,
t_factors,
) AS(values(
    'WA',
    '{1,2}', --factors
)) 
            SELECT 
                            dr_sex, 
                            COUNT(*) as all_crashes, 
                            COUNT(t1.id) filter (WHERE severity_id >= 3) as fsi_crashes,
                            COUNT(t1.id) filter (WHERE severity_id = 3) as si_crashes,
                            COUNT(t1.id) filter (WHERE severity_id = 4) as fatal_crashes
            FROM 
                            crash t1
            ,myvars
            WHERE 
                            (jurisdiction = t_state OR t_state is null)
                                                        AND (( CASE 
                                    WHEN 1 = ANY (t_factors) THEN '[subqry for alcohol_inv = true]'
                                    WHEN 2 = ANY (t_factors) THEN '[subqry for drugs_inv = true]'
END) factor
                                 
                                 OR
                                 t_factors is null)
AND severity_id > 1
                            AND dr_sex = ANY( '{Male, Female}'::text[] )
            GROUP BY dr_sex

Does this answer your question? CASE...WHEN in WHERE clause in Postgresql — Luuk
– Luuk, Commented Sep 15, 2022 at 6:50

Stefanov.sm · Accepted Answer · 2022-09-15 07:47:21Z

1

Since alcohol_inv is boolean then [subqry for alcohol_inv = true] is simply alcohol_inv. Maybe this query will do the job then

WITH myvars(t_state, t_factors) AS
(
 values ('WA', '{1,2}'::integer[])
) 
SELECT 
  dr_sex, 
  COUNT(*) as all_crashes, 
  COUNT(t1.id) filter (WHERE severity_id >= 3) as fsi_crashes,
  COUNT(t1.id) filter (WHERE severity_id = 3)  as si_crashes,
  COUNT(t1.id) filter (WHERE severity_id = 4)  as fatal_crashes
FROM 
  crash t1, myvars
WHERE 
  (jurisdiction = t_state OR t_state is null) 
  AND 
  (
   CASE WHEN 1 = ANY(t_factors) THEN alcohol_inv 
        WHEN 2 = ANY(t_factors) THEN drugs_inv END
   OR t_factors is null
  )
  AND severity_id > 1
  AND dr_sex in ('Male', 'Female')
GROUP BY dr_sex;

edited Sep 15, 2022 at 7:47

answered Sep 15, 2022 at 7:41

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

append subquery based on case- postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related