Sum two columns values in same table on postgresql query

Question

SELECT c.period,
    c.idsr_incident_id_id,
    c.idsr_disease_id_id,
    CASE WHEN idsr_incident_id_id = 1 OR idsr_incident_id_id = 3 THEN SUM(c.data_value::integer) ELSE 0 END  AS cases,
    CASE WHEN idsr_incident_id_id = 2 OR idsr_incident_id_id = 4 THEN SUM(c.data_value::integer) ELSE 0 END  AS deaths
   FROM veoc_idsr_weekly_national_report c
     LEFT JOIN veoc_idsr_diseases b ON b.id = c.idsr_disease_id_id
     LEFT JOIN veoc_idsr_reported_incidents j ON j.id = c.idsr_incident_id_id
    WHERE c.idsr_incident_id_id >= 1 AND c.idsr_incident_id_id <= 4 AND idsr_disease_id_id = 10 AND period ='2019W30'
  GROUP BY c.period, c.idsr_incident_id_id, c.idsr_disease_id_id, c.data_value;`enter code here`

Below my query results:

I want to sum the cases and the death value columns since the period and disease_id is the same. I think my problem is on the Group By section but I cant solve it. If it means i must remove the incident_id column to get the totals it will still be fine.

Vikram Jain · Accepted Answer · 2020-01-27 09:48:31Z

1

Please, try with below query where sum of function place replaced:

SELECT c.period,
    c.idsr_disease_id_id,
    SUM(CASE WHEN idsr_incident_id_id = 1 OR idsr_incident_id_id = 3 THEN (c.data_value::integer) ELSE 0 END)  AS cases,
    SUM(CASE WHEN idsr_incident_id_id = 2 OR idsr_incident_id_id = 4 THEN (c.data_value::integer) ELSE 0 END)  AS deaths
FROM veoc_idsr_weekly_national_report c
LEFT JOIN veoc_idsr_diseases b ON b.id = c.idsr_disease_id_id
LEFT JOIN veoc_idsr_reported_incidents j ON j.id = c.idsr_incident_id_id
WHERE c.idsr_incident_id_id >= 1 AND c.idsr_incident_id_id <= 4 AND idsr_disease_id_id = 10 AND period ='2019W30'
GROUP BY c.period, c.idsr_disease_id_id;

answered Jan 27, 2020 at 9:48

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

Sum two columns values in same table on postgresql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related