mysql - Sum columns with similar names in SQL

Question

Relatively new to SQL and want to shorten a query I’m using.

The goal is to add the total spent in one year and compare it to the next year. However, the column names are all formatted “Spend_YYYYMM” so “Spend_202102.”

Currently, my solution is just to add all 12 columns up:

SELECT
“Full_Name”,
(“Spend_202001”+”Spend_202002”...) AS “2020 Total”,
(“Spend_201901”+”Spend_201902”...) AS “2019 Total”
FROM “Customers”
WHERE “2019 Total” > “2020 Total”;

So is there a way to look for columns where it starts with “Spend_2019” and add them up without having to type all 12 columns out? Or is what I have the only way we can really do this?

(Sorry for all the superfluous quotes, it’s apparently how our DB works with SQL.)

Thank you for your help!!

question from:https://stackoverflow.com/questions/66068718/sum-columns-with-similar-names-in-sql

Answer 1

First, do not use identifiers that need to be escaped.

Second, your data model is weak. You should have separate rows for the different years.

But, the answer to your question is a MySQL extension of the HAVING clause:

SELECT Full_Name,
       (Spend_202001 + Spend_202002 ...) AS Total_2020,
       (Spend_201901 + Spend_201902 ...) AS Total_2019
FROM Customers
HAVING Total_2019 > Total_2020 ;