append subquery based on case- postgresql

Samra 0
Viewed 28

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
correlated-subquery switch-statement postgresql
1 Answers

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;
Stefanov.sm0
Related