Post reply

count by another count

  • June 18, 2008 at 9:50 am

    #187303

    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

  • June 18, 2008 at 1:14 pm

    #830716

    Edited: Sorry, i mis-read your question.

  • June 18, 2008 at 1:49 pm

    #830736

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

  • June 18, 2008 at 2:02 pm

    #830744

    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.

  • June 18, 2008 at 2:12 pm

    #830750

    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?

  • June 18, 2008 at 2:21 pm

    #830756

    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

  • June 18, 2008 at 2:36 pm

    #830769

    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)
    Intro to Tally Tables and Functions

  • June 18, 2008 at 2:45 pm

    #830775

    Hi All,

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

    Thanks.

    Ram

  • June 18, 2008 at 3:02 pm

    #830794

    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


    My blog: http://jahaines.blogspot.com

  • June 18, 2008 at 5:48 pm

    #830848

    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)
    Intro to Tally Tables and Functions

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

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