Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    india
    Posts
    15

    Question Unanswered: need help to built one sql query

    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

  2. #2
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116

    Re: need help to built one sql query

    The following should work and get you started:

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

  3. #3
    Join Date
    Jul 2003
    Location
    india
    Posts
    15

    Re: need help to built one sql query

    Originally posted by gannet
    The following should work and get you started:

    Code:
    SELECT a.num, COUNT(a.num)
    FROM
      (SELECT COUNT(*)
       FROM test_source
       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;

    Thanking You,
    Chalam N

  4. #4
    Join Date
    Jul 2003
    Location
    india
    Posts
    15

    Re: need help to built one sql query

    Originally posted by gannet
    The following should work and get you started:

    Code:
    SELECT a.num, COUNT(a.num)
    FROM
      (SELECT COUNT(*)
       FROM test_source
       GROUP BY age) a
    GROUP BY a.num
    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

  5. #5
    Join Date
    Jul 2003
    Location
    india
    Posts
    15

    Re: need help to built one sql query

    Originally posted by gannet
    The following should work and get you started:

    Code:
    SELECT a.num, COUNT(a.num)
    FROM
      (SELECT COUNT(*)
       FROM test_source
       GROUP BY age) a
    GROUP BY a.num
    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

  6. #6
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    Ooops many apologies for the omission, brain to hand co-ordination failure on my part. Glad to have helped.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •