CODEWITHSUNDEEP
mysqlsql
JPFichera
JPFichera

Reputation: 1

Sum columns with similar names in SQL

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!!

Upvotes: 0

Views: 519

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

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 ;

Upvotes: 1

Related Questions