0

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

3
Contributors
5
Replies
8
Views
8 Years
Discussion Span
Last Post by peter_budo
0

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!

0

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!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.