MySQL - SUM total values for each column grouped

Question

I have a query which returns records grouped by month and calculates the SUM of records on a table based in a condition:

SELECT USERS.ID AS ID, PROPERTIES.PROP_TYPE AS TIPO
,SUM(IF(MONTH(PROPERTIES.created_at) = 1, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as ENE
,SUM(IF(month(PROPERTIES.created_at) = 2, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS FEB
,SUM(IF(month(PROPERTIES.created_at) = 3, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as MAR
,SUM(IF(month(PROPERTIES.created_at) = 4, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS ABR
,SUM(IF(month(PROPERTIES.created_at) = 5, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS MAY
,SUM(IF(month(PROPERTIES.created_at) = 6, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUN
,SUM(IF(month(PROPERTIES.created_at) = 7, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUL
,SUM(IF(month(PROPERTIES.created_at) = 8, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS AGO
,SUM(IF(month(PROPERTIES.created_at) = 9, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as SEP
,SUM(IF(month(PROPERTIES.created_at) = 10, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as OCT
,SUM(IF(month(PROPERTIES.created_at) = 11, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as NOV
,SUM(IF(month(PROPERTIES.created_at) = 12, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as DIC
FROM PROPERTIES
LEFT JOIN USERS ON PROPERTIES.PROP_CAPPER_EMAIL = USERS.EMAIL_LOCAL_CRM
WHERE   USERS.ID = 1283
GROUP BY PROPERTIES.PROP_TYPE, USERS.ID

The output of this query is:

What I'm needing is to also SUM all the values for each month an have a final row with that result, the expected output is:

How can I adjust my query for getting this last row with columns vales SUM?

Regards!

Answer 1

The simplest way is to use with rollup:

 GROUP BY PROPERTIES.PROP_TYPE, USERS.ID WITH ROLLUP

This gives you separate rows for each subgroup, though.

Alternatively, you can use a CTE:

with t as (
      <your query here>
     )
select t.*
from t
union all
select null, null, sum(ene), sum(feb), . . .
from t;

Answer 2

Somebody post me a comment with a UNION approach and the comment was deleted, but I read it ones.

So, I believe that the solution is to add this to query:

UNION
SELECT USERS.ID AS ID, 'Total' AS TIPO
    ,SUM(IF(MONTH(PROPERTIES.created_at) = 1, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as ENE
    ,SUM(IF(month(PROPERTIES.created_at) = 2, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS FEB
    ,SUM(IF(month(PROPERTIES.created_at) = 3, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as MAR
    ,SUM(IF(month(PROPERTIES.created_at) = 4, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS ABR
    ,SUM(IF(month(PROPERTIES.created_at) = 5, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS MAY
    ,SUM(IF(month(PROPERTIES.created_at) = 6, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUN
    ,SUM(IF(month(PROPERTIES.created_at) = 7, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUL
    ,SUM(IF(month(PROPERTIES.created_at) = 8, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS AGO
    ,SUM(IF(month(PROPERTIES.created_at) = 9, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as SEP
    ,SUM(IF(month(PROPERTIES.created_at) = 10, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as OCT
    ,SUM(IF(month(PROPERTIES.created_at) = 11, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as NOV
    ,SUM(IF(month(PROPERTIES.created_at) = 12, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as DIC
FROM PROPERTIES
LEFT JOIN USERS ON PROPERTIES.PROP_CAPPER_EMAIL = USERS.EMAIL_LOCAL_CRM
WHERE   USERS.ID = 755
GROUP BY USERS.ID

With that UNION I gest this result with different rows data from original post:

So final query will be:

SELECT USERS.ID AS ID, PROPERTIES.PROP_TYPE AS TIPO
,SUM(IF(MONTH(PROPERTIES.created_at) = 1, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as ENE
,SUM(IF(month(PROPERTIES.created_at) = 2, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS FEB
,SUM(IF(month(PROPERTIES.created_at) = 3, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as MAR
,SUM(IF(month(PROPERTIES.created_at) = 4, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS ABR
,SUM(IF(month(PROPERTIES.created_at) = 5, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS MAY
,SUM(IF(month(PROPERTIES.created_at) = 6, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUN
,SUM(IF(month(PROPERTIES.created_at) = 7, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUL
,SUM(IF(month(PROPERTIES.created_at) = 8, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS AGO
,SUM(IF(month(PROPERTIES.created_at) = 9, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as SEP
,SUM(IF(month(PROPERTIES.created_at) = 10, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as OCT
,SUM(IF(month(PROPERTIES.created_at) = 11, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as NOV
,SUM(IF(month(PROPERTIES.created_at) = 12, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as DIC
FROM PROPERTIES
LEFT JOIN USERS ON PROPERTIES.PROP_CAPPER_EMAIL = USERS.EMAIL_LOCAL_CRM
WHERE   USERS.ID = 755
GROUP BY PROPERTIES.PROP_TYPE, USERS.ID
UNION
SELECT USERS.ID AS ID, 'Total'
,SUM(IF(MONTH(PROPERTIES.created_at) = 1, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as ENE
,SUM(IF(month(PROPERTIES.created_at) = 2, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS FEB
,SUM(IF(month(PROPERTIES.created_at) = 3, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as MAR
,SUM(IF(month(PROPERTIES.created_at) = 4, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS ABR
,SUM(IF(month(PROPERTIES.created_at) = 5, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS MAY
,SUM(IF(month(PROPERTIES.created_at) = 6, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUN
,SUM(IF(month(PROPERTIES.created_at) = 7, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUL
,SUM(IF(month(PROPERTIES.created_at) = 8, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS AGO
,SUM(IF(month(PROPERTIES.created_at) = 9, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as SEP
,SUM(IF(month(PROPERTIES.created_at) = 10, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as OCT
,SUM(IF(month(PROPERTIES.created_at) = 11, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as NOV
,SUM(IF(month(PROPERTIES.created_at) = 12, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as DIC
FROM PROPERTIES
LEFT JOIN USERS ON PROPERTIES.PROP_CAPPER_EMAIL = USERS.EMAIL_LOCAL_CRM
WHERE   USERS.ID = 755
GROUP BY USERS.ID