Dracoro

Have some tables with multiple columns and want to count the values in each column.

e.g. columns in table are a1, a2, ....a99.

All column hold different types of data.

So, for example, I would expect results as follows:
col, val, count
a1, 89, 232
a1, 23, 9876
a1, 1221, 23
a2, 'Bob', 234
a2, 'Fred', 4322
a3, 'The times', 543
a3, 'Telegraph', 22231
etc......

finalzero

select
count(a1) as 'Column 1',
count(a2) as 'Column 2',
count(a3) as 'Column 3',
count(a4) as 'Column 4',
count(a5) as 'Column 5'
from alist

Dracoro

thanks, but I need a group of all possible values in a1, a2 etc.

AlexJReid

select count(col) as c, col from table group by col;

and repeat for each column you want to do this on. could do some stuff with pivots and systables if you wanted to automate it!

GCollier

What database are you using?

GaryK

If you need to count all columns then you need to include all columns in the select statement, long-winded yes unless you write a stored proc which will build the sql for you and use expression evaluation to run the dynamic sql. Bottom line is its very poorly modelled (which may or may not be something you can change).

Dracoro

Exactly, just how a client datbase storing it's data :banghead:

on mysql - which hasn't impressed me tbh coming from a sqlserver/oracle/db2 background!