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?