SQL for counting number of times a varchar repeated in a table

[raghunath1981]

Hi All,
Could you please help me with writing a sql for counting number of times a varchar (1000) repeated in the table. I think I cannot use groupby/distinct etc.
Please give me an idea how I could do that if I want the output
such that each row is the varchar (distinct) and number times it repeated.
I was trying several things but it did not work :(
select tab1.varchar_col , tab2.CNT from tab tab1 ( select count(*) from tab group by varchar_col) as tab2 ...

Thanks,
Raghu

[docdiesel]

Hi,

SELECT
  varchar_col, count(varchar_col) as cnt
FROM
  tab1
GROUP BY
  varchar_col;

should work.

Regards,

Bernd

[raghunath1981]

Hi Bernd,

Thanks a lot for the reply.. i tried it but I am getting the error below.
I was wondering if we can user varchar in group by at all ?...

SQL1585N A system temporary table space with sufficient page size
does not exist.

Thanks,
Raghu

[docdiesel]

Hi,

your rows are to big for the 4K standard temp tablespace, where sorts etc. are to be done. Create a system temp. tablespace with 8K, maybe 16K size, as the error message says ("CREATE SYSTEM TEMPORARY TABLESPACE"). See also IBMs documentation at http://publib.boulder.ibm.com/infoce...n/t0005216.htm .

Regards,

Bernd