informix 11.5 How to count the number of members of a column separately

Question

I have a table that have a column like this:

table1:

c1 c2  c3 
 . a   .
 . a   .
 . a   .
   a
   b
   b  
   c

How to get a result like the following?:

-- a            b           c
        
 count(a)   count(b)   count(c)

Of course, there is an auxiliary table like the one below:

--field table 
d1 d2 
   a
   b
   c

Answer 1

^{Transferring comments into an answer.}

If there was an entry in table1.c2 with d as the value, is it correct to guess/assume that you'd want a fourth column of output with the name d and the count of the number of d values as the value. And there'd be an extra row in the auxilliary table too. That's pretty tricky.

You'd probably be better off with a result table with N rows, one for each value in the table1.c2 column, with the first column identifying the value and the second the count:

SELECT c2, COUNT(c2) FROM table1 GROUP BY c2 ORDER BY c2

To generate a single row with the names and counts as shown requires a dynamically built SQL statement — you write an SQL statement that generates the SQL (or the key components of the SQL) for a second statement that you actually execute to get the result. The main reason for it being dynamic like that is that the number of columns in the result set is not known until you run a query that determines which values exist in table1.c2. That's non-trivial — doable, but non-trivial.

I forget whether 11.50 has a built-in sysmaster:sysdual table. I ordinarily use a regular one-column, one-row table called dual. You can get the result you want, if your Table1.C2 has values a through e in it, with:

SELECT (SELECT COUNT(*) FROM Table1 WHERE c2 = 'a') AS a,
       (SELECT COUNT(*) FROM Table1 WHERE c2 = 'b') AS b,
       (SELECT COUNT(*) FROM Table1 WHERE c2 = 'c') AS c,
       (SELECT COUNT(*) FROM Table1 WHERE c2 = 'd') AS d,
       (SELECT COUNT(*) FROM Table1 WHERE c2 = 'e') AS e
  FROM dual;

This gets the information you need. I don't think it is elegant, but "works" beats "doesn't work".