By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,226 Members | 1,032 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,226 IT Pros & Developers. It's quick & easy.

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

P: 2
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
Dec 7 '07 #1
Share this Question
Share on Google+
3 Replies


docdiesel
Expert 100+
P: 297
Hi,

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   varchar_col, count(varchar_col) as cnt
  3. FROM
  4.   tab1
  5. GROUP BY
  6.   varchar_col;
should work.

Regards,

Bernd
Dec 8 '07 #2

P: 2
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
Dec 9 '07 #3

docdiesel
Expert 100+
P: 297
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
Dec 10 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.