CODEWITHSUNDEEP
postgresql
jeffa.david
jeffa.david

Reputation: 45

Sum two columns values in same table on postgresql query

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:

My results from the above query

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.

Upvotes: 0

Views: 163

Answers (1)

Vikram Jain
Vikram Jain

Reputation: 5588

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;

Upvotes: 1

Related Questions