MySQL min and max of each column

Question

I can run the following to dynamically identify all decimal fields in table with many columns:

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='tbl_name' AND DATA_TYPE = 'decimal'

How would I pull the min and max value for each of those fields? e.g. final output like:

COLUMN_NAME  DATA_TYPE  MIN_VAL  MAX_VAL
a            decimal    4        22
b            decimal    18       5593
c            decimal    1        299

UPDATE:

Here is the final syntax I used to get this working. Maybe I missed an easier way but this is working, so thanks to Gordon Linoff for the answer.

set @sql = concat('SELECT ', @cols, ' FROM ', @t);

SELECT @sql := GROUP_CONCAT(REPLACE(REPLACE(@sql, @cols,
                                            CONCAT('"', COLUMN_NAME, '" as TheCol', ', ', '"', DATA_TYPE, '" as TheDType', ', ',
                                                   'MIN(', COLUMN_NAME, ') as TheMin, MAX(', COLUMN_NAME, ') as TheMax'
                                                  )
                                           ),
                                    @t, 'tbl_name') SEPARATOR ' union all '
                            )              
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='tbl_name' AND DATA_TYPE = 'decimal';

prepare s from @sql;
execute s;
deallocate prepare s;

Wrte a dynamic SQL statement based on the tableName and ColumnName to get the min max... — xQbert
– xQbert, Commented May 8, 2016 at 16:48

Gordon Linoff · Accepted Answer · 2016-05-09 01:18:53Z

2

You need to use dynamic sql:

set @sql = 'SELECT @cols FROM @t';

SELECT @sql := GROUP_CONCAT(REPLACE(REPLACE(@sql, @cols,
                                            CONCAT(COLUMN_NAME, ', ', DATA_TYPE, ', ',
                                                   'MIN(', COLUMN_NAME, '), MAX(', COLUMN_NAME, ')'
                                                  )
                                           ),
                                    @t, 'tbl_name') SEPARATOR ' union all '
                            )              
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='tbl_name' AND DATA_TYPE = 'decimal';

prepare s from @sql;
execute s;
deallocate prepare s;

edited May 9, 2016 at 1:18

answered May 8, 2016 at 16:56

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dylan Over a year ago

Throwing a syntax error on the SEPARATOR line. I obviously replaced the actual table name in the 2 places referenced - are any other changes needed?

Collectives™ on Stack Overflow

MySQL min and max of each column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related