Hi, Could anybody please give me Solution for the following Problem.

Table Name: Test_Source

NAME AGE QUALIFIES
---------- ----------- -----------
Test0 26 9
Test1 21 9
Test2 15 0
Test3 15 0
Test4 21 9
Test5 45 4
Test6 20 9
Test7 20 9

The above table Test_Source is the source table. I need to write a query, which will generate data like following from the Test_Source.


Table Name: Test_Result

NoofOccurs Count
------------ ------------------
1 2
2 3
3 0
4 0

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.

Thanks in Advance,

Chalam N

The following should work and get you started:

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;

Thanking You,
Chalam N

Hi gannet,

sorry for sending reply that your given query is not giving results,

your given query is executing with good performance with a little modification in that one as follows.

SELECT a.num, COUNT(a.num)
FROM
(SELECT COUNT(*) as num
FROM test_source
GROUP BY age) a
GROUP BY a.num

Thank you very much,

Chalam N

Hi gannet,

sorry for sending reply that your given query is not giving results,

your given query is executing with good performance with a little modification in that one as follows.

SELECT a.num, COUNT(a.num)
FROM
(SELECT COUNT(*) as num
FROM test_source
GROUP BY age) a
GROUP BY a.num

Thank you very much,

Chalam N

Ooops many apologies for the omission, brain to hand co-ordination failure on my part. Glad to have helped.