# Forum

### How can I do max,min for all columns in a table?

Contributor
Offline
Posts: 185
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

### How can I do max,min for all columns in a table?

I have some tables/views which are constantly being changed, they contain only numeric data, how do I find the max, min and other statistics of each column?
Contributor
Offline
Posts: 185
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

### Re: How can I do max,min for all columns in a table?

Using KogScript it is easy to create a function to create a view of the tables containing the max and min of each column.

Code: Select all

``````function max_min(name, view)
local t = select top 1 * from \$name;
local s = "create view ".. view .. " as select "
for i=0,#t.colNames do
s = s .. "max(" .. t.colNames[i] .. "), min(" .. t.colNames[i] .. ")";
end
s = s .. " from " .. name;
return s;
end

sql( max_min("s1.t1", "s1.v1"));``````
Contributor
Offline
Posts: 185
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

### Re: How can I do max,min for all columns in a table?

Alternatively a table may be created showing the statistics from multiple tables with the same numbers of columns.

Code: Select all

``````function max_min(name, table)
local t = select top 1 * from \$name;
local s = "insert into ".. table .. "  select '" .. name .."'"
for i=0,#t.colNames do
s = s .. ", max(" .. t.colNames[i] .. "), min(" .. t.colNames[i] .. ")";
end
s = s .. " from " .. name;
return s;
end

function create_max_min_table(name, table)
drop table \$table;
local t = select top 1 * from \$name;
local s = "create table ".. table .. " ( tname varchar(255)"
for i=0,#t.colNames do
s = s .. ", " .. t.colNames[i] .. "_max int, " .. t.colNames[i] .. "_min int";
end
s = s .. " ) ";
sql( s );
end

-- create a couple of tables
drop table s1.t5;
create table s1.t5 (a int, b int, c int);
for i=1,20 do
insert into s1.t5 values(\$i, \$( i*7 % 53), \$(3-i) );
end

drop table s1.t6;
create table s1.t6 (a int, b int, c int);
for i=1,20 do
insert into s1.t6 values(\$i, \$( i*11 % 101), \$(15-i) );
end

create_max_min_table("s1.t5", "s1.max_min")
sql( max_min("s1.t5", "s1.max_min"));
sql( max_min("s1.t6", "s1.max_min"));
``````

### Who is online

Users browsing this forum: No registered users and 1 guest