Mysql Sum() multiple columns

Power of dynamic SQL

When dealing with numerous columns, dynamically concat column names to keep your sanity intact:

SET @sql = NULL;
SELECT GROUP_CONCAT(CONCAT('SUM(COALESCE(', COLUMN_NAME, ', 0))')) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table' AND COLUMN_NAME LIKE 'col%';

SET @sql = CONCAT('SELECT ', @sql, ' FROM your_table');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Confucius say: "He who creates dynamic SQL has more time for coffee."


For clarity in your returns, rename the aggregated columns with meaningful aliases:

SELECT 
  SUM(col1) AS 'Total_Red',
  SUM(col2) AS 'Total_Green',
  SUM(col3) AS 'Total_Blue'
FROM 
  your_table;
-- Fun fact: SQL aliases are like nickname, but for columns.


Schema tuning

Considering a normalized database with separate Marks table, put the JOIN keyword to work:

SELECT 
  s.student_id,
  SUM(m.mark) AS TotalMarks
FROM 
  students s
INNER JOIN marks m ON s.student_id = m.student_id
GROUP BY 
  s.student_id;


Boost your query's performance

To boost performance with many columns, index the columns used in SUM. Your database will thank you for the quick data access.

Keep the code clean

Maintain a clean, readable code. Proper indentation and formatting are the chocolate chips in your programming cookie.

SQL on the fly

Dynamic SQL has shiny armor for scalability with summing unknown or dynamically named columns. Here's the general strategy:

1. INFORMATION_SCHEMA gets column names.
2. Craft summation SQL string with GROUP_CONCAT() and CONCAT().
3. Let prepared statements execute the query.

This technique layers efficiency onto queries and adapts to ever-changing table schemas.

NULLs, we've got them covered

NULL values can throw results off kilter. Rein it in with COALESCE() or IFNULL(). Your NULLs will be zeros in no time:

SELECT COALESCE(SUM(col1), 0) as Col1NonNullSum FROM your_table;
-- SQL lifehack: If life gives you NULLs, turn them to zeros.
