SUM multiple columns building column names as string in MySQL 5.7

20 Views Asked by At

Unfortunately, I am only able to utilize MySQL 5.7.

I have a table that has a series of columns named for each month. My goal is to sum the total for each column dynamically that is relevant based on the current month for Year To Date.

Here is the full Query (NOTE: it is January so instead of > CONCAT('sales'...) to test my query I will use <= CONCAT('sales'...):

SELECT
  SUM(CONVERT(CrntMths.string,CHAR)) AS goalSaYTD
FROM devsalesgoals ds
  JOIN (SELECT
      GROUP_CONCAT('ds.', `COLUMN_NAME` SEPARATOR ' + ') AS string
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_NAME` = 'devsalesgoals'
    AND `COLUMN_NAME` LIKE 'sales%'
    AND `COLUMN_NAME` > CONCAT('sales', DATE_FORMAT(CURDATE(), '%m'))) AS CrntMths
WHERE ds.year = YEAR(CURDATE())

It incorrectly returns a value of 0. It should be 29 based on manual calculation from the table.

This subquery correctly produces the string of column names :

SELECT
  GROUP_CONCAT('ds.', `COLUMN_NAME` SEPARATOR ' + ') AS string
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_NAME` = 'devsalesgoals'
AND `COLUMN_NAME` LIKE 'sales%'
AND `COLUMN_NAME` > CONCAT('sales', DATE_FORMAT(CURDATE(), '%m'))

Which produces this varchar string: string ds.sales02 + ds.sales03 + ds.sales04 + ds.sales05 + ds.sales06 + ds.sales07 + ds.sales08 + ds.sales09 + ds.sales10 + ds.sales11 + ds.sales12

It seems SUM is not able to consume the string (CONVERT(CrntMths.string,CHAR))

What am I missing?

0

There are 0 best solutions below