count by another count

  • Hi All,

    I used count to contactid,contact address and i want to total of contact for each contact.

    SELECT StudentNo, COUNT(ContactId) AS contact

    FROM StudentContact

    GROUP BY StudentNo

    ORDER BY contact DESC\

    stdentno conactid

    100 8

    101 6

    102 6

    103 6

    104 6

    105 6

    106 6

    meaning is conatcid=8 is the student has given 8 conact address sofar likewise 6 conactaddress for 101 studentno... etcc.

    now i want to count all conatc id 6, 8 etc..

    Any help appreciated..

    Thanks

    Ram

  • Edited: Sorry, i mis-read your question.

  • I would add the result into a #tempTable and use your group by to count again

  • This should do it all in one query..

    select con.contact, count(con.contact) as CountOfContact from

    (select studentno, count(contactid) as Contact

    from StudentContact

    group by studentno) con

    group by con.contact

    order by count(con.contact) desc

    Hope this is what you mean.

    Matt.

  • If the set isn't too big

    select contact,count(studentno) as freq

    from (

    SELECT StudentNo, COUNT(ContactId) AS contact

    FROM StudentContact

    GROUP BY StudentNo

    ) t

    group by contact

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • ramaanujamr (6/18/2008)


    Hi All,

    I used count to contactid,contact address and i want to total of contact for each contact.

    SELECT StudentNo, COUNT(ContactId) AS contact

    FROM StudentContact

    GROUP BY StudentNo

    ORDER BY contact DESC

    stdentno conactid

    100 8

    101 6

    102 6

    103 6

    104 6

    105 6

    106 6

    meaning is conatcid=8 is the student has given 8 conact address sofar likewise 6 conactaddress for 101 studentno... etcc.

    now i want to count all conatc id 6, 8 etc..

    Any help appreciated..

    Thanks

    Ram

    Can you post how you want the output to look like ?


    * Noel

  • Ummm... take a look at the following subjects in Books Online...

    ROLLUP operator

    CUBE operator

    GROUPING keyword

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi All,

    This is what i want . Great. I just test with CUBE,ROLLEUP operator. Thanks for everyone.

    Thanks.

    Ram

  • This solution can easily be handled via a derived table, cte or the over clause. The below example already has the first set of counts in place, so you need only change the query accordingly.

    A few examples:

    declare @t table(

    studentno int,

    contactid int

    )

    insert into @t values (100,8);

    insert into @t values (101,6);

    insert into @t values (101,6);

    insert into @t values (102,6);

    insert into @t values (103,6);

    insert into @t values (104,6);

    insert into @t values (105,6);

    insert into @t values (106,6);

    --method 1

    select a.studentno, a.contactid, b.total

    from @t a

    inner join(

    select contactid, count(contactid) as total

    from @t

    group by contactid

    ) as b

    on a.contactid = b.contactid

    --method 2

    select studentno,contactid, count(contactid) over(partition by contactid)

    from @t

  • ramaanujamr (6/18/2008)


    Hi All,

    This is what i want . Great. I just test with CUBE,ROLLEUP operator. Thanks for everyone.

    Thanks.

    Ram

    Thanks for the feedback, Ram.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply