1,105,221 Community Members

SUM() all number fields individually

Member Avatar
grant.baker
Light Poster
35 posts since Aug 2010
Reputation Points: 10 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

I'm trying to sum all numeric fields individually. I won't always know the field names, otherwise I could manually construct the query SELECT SUM(Field1) as Field1, SUM(Field2) as Field2...etc FROM myTable Is there a way to construct this on the fly in mysql? I'm thinking there's probably a way to incorporate SELECT COLUMN_NAME FROM information_schema WHERE TABLE_NAME=myTableName AND DATA_TYPE IN('tinyint','decimal'), but I'm not sure how. Thanks!

Member Avatar
pritaeas
mod_pritaeas
11,291 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,832 [?]
Skill Endorsements: 155 [?]
Moderator
Featured
Sponsor
 
0
 

You need to build your query (string concatenation) using the information in the information_schema and then execute it.

Member Avatar
grant.baker
Light Poster
35 posts since Aug 2010
Reputation Points: 10 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

I know how to do that with a separate query and php. Is there a way to do it all in one mysql query?

Member Avatar
pritaeas
mod_pritaeas
11,291 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,832 [?]
Skill Endorsements: 155 [?]
Moderator
Featured
Sponsor
 
1
 

No, not in a single one. You could do it in a stored procedure.

Member Avatar
grant.baker
Light Poster
35 posts since Aug 2010
Reputation Points: 10 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

Thanks for the help! This is what I ended up doing:

$results= mysql_fetch_array(mysql_query("SELECT CONCAT('SELECT SUM(',GROUP_CONCAT(INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME SEPARATOR '), SUM('), ') FROM ',INFORMATION_SCHEMA.COLUMNS.TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = '$logbook' AND INFORMATION_SCHEMA.COLUMNS.DATA_TYPE IN('tinyint','decimal')"));

That results in an array with the desired query as the value. Then I can run another query with the array value.

Question Answered as of 11 Months Ago by pritaeas
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: