Hi,
I'm reasonably new to coding so this may be a really simple fix (i hope anyway!)
I'm building a query table to identify cases for inclusion in a healthcare quality indicator as well as produce an output column that provides a true or false statement as to whether the indicator has been met. Query 1 below works fine but when I want to add another OR statement in the IF statement (shown in query 2 below query1) I get this:
Error Details:
Query Error: Improper usage of GROUP BY clause.
Please ensure that all non-aggregate columns used in the SELECT clause are also used in GROUP BY clause.
**Query 1 - this works**
SELECT
jobs.id AS job_id,
patients.id AS patient_id,
jobs."date" AS "date",
strokes.id,
strokes."fast" AS "fast",
strokes.fast_neg AS fast_neg,
strokes.weak_side AS weakness,
strokes.face_side AS face_weakness,
strokes.resolved AS resolved,
strokes.speech AS speech,
SUM(observations.bm) AS total_bm,
MAX(bloodtests.value) AS bm,
SUM(observations.sbp) AS total_sbp,
primaries.total_gcs AS primary_gcs,
patients.mechanism_id AS mechanism_id,
jobs.cad_cat_id AS cadcat_id,
jobs.job_type AS job_type,
strokes.cb_inclusion AS cb_inclusion,
IF((strokes."fast" = 'true'
OR strokes.weak_side IS NOT NULL
OR strokes.face_side IS NOT NULL
OR strokes.speech IS NOT NULL)
AND ((SUM(observations.bm) > 1)
OR MAX(bloodtests.value) > 0)
AND (SUM(observations.sbp) > 1), 'Complete CB', 'Incomplete CB') AS CB_completion
FROM patients
JOIN jobs ON patients.job_id = jobs.id
LEFT JOIN strokes ON patients.id = strokes.patient_id
LEFT JOIN observations ON patients.id = observations.patient_id
LEFT JOIN bloodtests ON patients.id = bloodtests.patient_id
LEFT JOIN primaries ON patients.id = primaries.patient_id
WHERE (patients.mechanism_id = '336'
OR strokes.id IS NOT NULL)
AND (jobs."job_type" != 'Interhospital transfer'
AND jobs.cad_cat_id != 44)
AND strokes.cb_inclusion != 'exclude'
AND patients.id NOT IN ( 2066 , 2115 , 3547 , 4045 , 4050 , 5782 , 5836 , 5910 )
GROUP BY jobs.id,
patients.id,
jobs."date",
strokes.id,
strokes."fast",
strokes.fast_neg,
strokes.weak_side,
strokes.face_side,
strokes.resolved,
strokes.speech,
primaries."total_gcs",
patients.mechanism_id,
jobs.cad_cat_id,
jobs.job_type,
strokes.cb_inclusion
ORDER BY jobs.id
SELECT
jobs.id AS job_id,
patients.id AS patient_id,
jobs."date" AS "date",
strokes.id,
strokes."fast" AS "fast",
strokes.fast_neg AS fast_neg,
strokes.weak_side AS weakness,
strokes.face_side AS face_weakness,
strokes.resolved AS resolved,
strokes.speech AS speech,
SUM(observations.bm) AS total_bm,
MAX(bloodtests.value) AS bm,
SUM(observations.sbp) AS total_sbp,
primaries.total_gcs AS primary_gcs,
patients.mechanism_id AS mechanism_id,
jobs.cad_cat_id AS cadcat_id,
jobs.job_type AS job_type,
strokes.cb_inclusion AS cb_inclusion,
IF((strokes."fast" = 'true'
OR strokes.weak_side IS NOT NULL
OR strokes.face_side IS NOT NULL
OR strokes.speech IS NOT NULL
OR primaries.total_gcs <14)
AND ((SUM(observations.bm) > 1)
OR MAX(bloodtests.value) > 0)
AND (SUM(observations.sbp) > 1), 'Complete CB', 'Incomplete CB') AS CB_completion
FROM patients
JOIN jobs ON patients.job_id = jobs.id
LEFT JOIN strokes ON patients.id = strokes.patient_id
LEFT JOIN observations ON patients.id = observations.patient_id
LEFT JOIN bloodtests ON patients.id = bloodtests.patient_id
LEFT JOIN primaries ON patients.id = primaries.patient_id
WHERE (patients.mechanism_id = '336'
OR strokes.id IS NOT NULL)
AND (jobs."job_type" != 'Interhospital transfer'
AND jobs.cad_cat_id != 44)
AND strokes.cb_inclusion != 'exclude'
AND patients.id NOT IN ( 2066 , 2115 , 3547 , 4045 , 4050 , 5782 , 5836 , 5910 )
GROUP BY jobs.id,
patients.id,
jobs."date",
strokes.id,
strokes."fast",
strokes.fast_neg,
strokes.weak_side,
strokes.face_side,
strokes.resolved,
strokes.speech,
primaries."total_gcs",
patients.mechanism_id,
jobs.cad_cat_id,
jobs.job_type,
strokes.cb_inclusion
ORDER BY jobs.id