June 18, 2008 at 9:50 am
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
Edited: Sorry, i mis-read your question.
June 18, 2008 at 1:49 pm
I would add the result into a #tempTable and use your group by to count again
June 18, 2008 at 2:02 pm
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
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
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
Ummm... take a look at the following subjects in Books Online...
ROLLUP operator
CUBE operator
GROUPING keyword
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2008 at 2:45 pm
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
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
June 18, 2008 at 5:48 pm
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply