SQL question - sub select against same table

[gamerland]

hello,
I have a set of data that should be simple to answer but I just cannot get it right.

DATATABLE

fld1 fld2
1 1
1 1
1 1
2 A
2 A
3 1
3 F
4 A
4 A

I am trying to figure out how many times fld2 is different within fld1. In this case 1 time for key 3.

As my attempts are either in error or produce useless results, I don't want to give anyone a bad start.
Thanks in advance.

 

[sathyarams]

with temp(fld1,fld2) as
(select distinct fld1,fld2 from table1)
select count(*) from temp

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[MarcLodge]

Hi Gamer,
I'm not sure I understand you post. You say that you are trying to figure out how many times fld2 is different within fld2, but then say that for key 3, it is 1 time. Is this a typo, because it looks to me that there are 2 values for key 3 (1 and F).
If I understand you correctly, then I think the following will work:
SELECT FLD1, COUNT(DISTINCT FLD2)
FROM DATATABLE
GROUP BY FLD1

Marc