In Test_Result table
1. The first row meaning is that 26,45 are only two ages, which have only one occurrence.
2. The second row meaning is that 21,15,20 are only three ages, which have each two occurrence.
3. The third row meaning is that there is no age which have three occurrences.
Originally posted by gannet
The following should work and get you started:
SELECT a.num, COUNT(a.num)
GROUP BY age) a
GROUP BY a.num
Hi Gannet ,Thank you for your kind help, but still the above query is not giving the result what i need. here i am getting result with combination of the following three queries . could you please suggest me one optimized way to get my results.
1.create table temp1 (age int, occurs int);
2.insert into temp1(age, occurs) (select age, count(*) from test_source group by age);
3. select occurs, count(*) from temp1 group by occurs order by occurs asc;