MYSQL SUM 2 values in a CASE statement

Question

Hi I've got the following query

SELECT 
ORG.`id`,

ORG.`organisation_name`,

SFCR.report_date_year 

,CASE 
    -- GWP
    WHEN (QRT_VALUES.`template_id` = 'S.05.01.02.01' AND QRT_VALUES.`row_id` = 'R0110' AND QRT_VALUES.`column_id` = 'C0200') THEN 'gross_written_premiums' 

    WHEN (QRT_VALUES.`template_id` = 'S.05.01.02.02' AND QRT_VALUES.`row_id` = 'R1410' AND QRT_VALUES.`column_id` = 'C0300') THEN 'gross_written_premiums' 

END AS Dimension 
,CASE
    -- GWP
    WHEN (QRT_VALUES.`template_id` = 'S.05.01.02.01' AND QRT_VALUES.`row_id` = 'R0110' AND QRT_VALUES.`column_id` = 'C0200' || 
          QRT_VALUES.`template_id` = 'S.05.01.02.02' AND QRT_VALUES.`row_id` = 'R1410' AND QRT_VALUES.`column_id` = 'C0300') 
    THEN  
        (
            (CASE 
                WHEN (QRT_VALUES.`template_id` = 'S.05.01.02.01' AND QRT_VALUES.`row_id` = 'R0110' AND QRT_VALUES.`column_id` = 'C0200') 
                THEN (QRT_VALUES.`value`) ELSE 0 END)
            +

            (CASE 
                WHEN (QRT_VALUES.`template_id` = 'S.05.01.02.02' AND QRT_VALUES.`row_id` = 'R1410' AND QRT_VALUES.`column_id` = 'C0300') 
                THEN (QRT_VALUES.`value`) ELSE 0 END)
        )

END AS Value,  

ORG.`parent_id` AS `parent_id`

FROM ird_qrt_values AS QRT_VALUES

LEFT JOIN `ird_reports` AS SFCR ON (QRT_VALUES.`report_id` = SFCR.`id` AND SFCR.`report_type_id` = 1)

LEFT JOIN `ird_organisations` AS ORG ON (SFCR.`organisation_id` = ORG.`id` AND SFCR.`report_type_id` = 1)

WHERE 
-- GWP
(QRT_VALUES.`template_id` = 'S.05.01.02.01' AND QRT_VALUES.`row_id` = 'R0110' AND QRT_VALUES.`column_id` = 'C0200') OR

(QRT_VALUES.`template_id` = 'S.05.01.02.02' AND QRT_VALUES.`row_id` = 'R1410' AND QRT_VALUES.`column_id` = 'C0300') AND 
SFCR.`qrt_processed` = 1 

HAVING organisation_name = 'Aviva Plc';

Currently this query produces the following result

id      organisation_name       report_date_year        Dimension                       Value
-------------------------------------------------------------------------------------------------------
351     Aviva Plc               2016                    gross_written_premiums          15672717.000
351     Aviva Plc               2016                    gross_written_premiums          9708280.000
351     Aviva Plc               2017                    gross_written_premiums          8234725.034
351     Aviva Plc               2017                    gross_written_premiums          9150979.428

What i am looking for is the SUM of the 2 numbers as one row for each year. See below table. I am not sure how to do this within the case statement.

id      organisation_name       report_date_year        Dimension                       Value
-------------------------------------------------------------------------------------------------------
351     Aviva Plc               2016                    gross_written_premiums          25380997.000
351     Aviva Plc               2017                    gross_written_premiums          17385704.462

Any help is greatly appreciated. Thanks in advance.

you can wrap it with an outer query and use sum(value) group by ... — isaace
– isaace, Commented Apr 30, 2018 at 13:11
It seems very strange to me to using a HAVING and WHERE clause together without GROUP BY. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 30, 2018 at 13:15
Normally, whenever you use a CASE construct you would include an ELSE. Not sure this is the issue but I would start by properly ending them. — FDavidov
– FDavidov, Commented Apr 30, 2018 at 13:17
hi guys thanks for getting back on this, @isaace, it would be great if you could show how i wrap it in outer query? i've tried that i reckon but may be done something different. thanks again. — bharath
– bharath, Commented Apr 30, 2018 at 13:17
Looking at that query, no need to wrap another query around the outside. A SUM() can be used around the CASE that generates the value column. Just add a group by clause at the end for all the other columns. I agree with @TimBiegeleisen , the HAVING clause is a bit odd. Seems probable that it can be changed to part of the WHERE clause. — Kickstart
– Kickstart, Commented Apr 30, 2018 at 13:46

Rahul Jain · Accepted Answer · 2018-04-30 13:17:07Z

2

Try this:

SELECT id, organisation_name, report_date_year, Dimension, SUM(Value) Value
FROM
(<Your current Query>) t
GROUP BY
id, organisation_name, report_date_year, Dimension

answered Apr 30, 2018 at 13:17

Rahul Jain

1,3997 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

MYSQL SUM 2 values in a CASE statement

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