Select SUM (All Fields in table)

[mayfieldjr]

I have a table with 32 fields.
Is there a shorter way - [ other than select sum(field1 + field2 + ... Field32] to get the sum of all fields for a date range.
I know, I know, once I create the query I can save it for future use but I have over 60 tables.

I just want to find [and maybe delete] all the entries that have a zero value for the sum of all fields.

Thanks

[mwasif]

I think you can achive this by using INFORMATION_SCHEMA tables.

[mayfieldjr]

schema tables? How? Where? I'll start reseaching that.
Thanks.

[mwasif]

Hello mayfieldjr,

Here is a sample code to get the SUM of all INT fields in a specific table. You must have at least MySQL 5.0 to run this query.

SET @qry = (SELECT CONCAT('SELECT SUM(',GROUP_CONCAT(INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME SEPARATOR '+'), ')
 FROM ',INFORMATION_SCHEMA.COLUMNS.TABLE_NAME,' 
 WHERE date_column BETWEEN \'2008-01-01\' AND \'2008-12-31\' ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'table_name'
AND INFORMATION_SCHEMA.COLUMNS.DATA_TYPE = 'int');
PREPARE stmt FROM @qry;
EXECUTE stmt;

If you just want to see the resultant query, use the below code.

SET @qry = (SELECT CONCAT('SELECT SUM(',GROUP_CONCAT(INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME SEPARATOR '+'), ')
 FROM ',INFORMATION_SCHEMA.COLUMNS.TABLE_NAME,' 
 WHERE date_column BETWEEN \'2008-01-01\' AND \'2008-12-31\' ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'table_name'
AND INFORMATION_SCHEMA.COLUMNS.DATA_TYPE = 'int');
SELECT @qry;

Have a good day!

[mayfieldjr]

You learn something new every day.
Thank you. Brilliant!!

I am new to this forum. Do I now [somehow] close this thread?
How?

Hello mayfieldjr,

Here is a sample code to get the SUM of all INT fields in a specific table. You must have at least MySQL 5.0 to run this query.

SET @qry = (SELECT CONCAT('SELECT SUM(',GROUP_CONCAT(INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME SEPARATOR '+'), ')
 FROM ',INFORMATION_SCHEMA.COLUMNS.TABLE_NAME,' 
 WHERE date_column BETWEEN \'2008-01-01\' AND \'2008-12-31\' ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'table_name'
AND INFORMATION_SCHEMA.COLUMNS.DATA_TYPE = 'int');
PREPARE stmt FROM @qry;
EXECUTE stmt;

If you just want to see the resultant query, use the below code.

SET @qry = (SELECT CONCAT('SELECT SUM(',GROUP_CONCAT(INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME SEPARATOR '+'), ')
 FROM ',INFORMATION_SCHEMA.COLUMNS.TABLE_NAME,' 
 WHERE date_column BETWEEN \'2008-01-01\' AND \'2008-12-31\' ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'table_name'
AND INFORMATION_SCHEMA.COLUMNS.DATA_TYPE = 'int');
SELECT @qry;

Have a good day!

[peter_budo]

"Mark as Solved" bellow last post...