COUNT for group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

COUNT for group by

Author

Topic

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2009-11-25 : 12:29:03

select s.survey_id
from survey s join survey_response sr on s.survey_id = sr.survey_id
join survey_answer sa on sa.answer_id = sr.answer_id
group by s.survey_id
having sum(sa.weight)= sum(sa.high)

Result
-----------
survey_id
443
446
450
451
453
458
459
461

but i want the count of the s.survey_id i.e 8

so i do this:

select count(s.survey_id )
from survey s join survey_response sr on s.survey_id = sr.survey_id
join survey_answer sa on sa.answer_id = sr.answer_id
group by s.survey_id
having sum(sa.weight)= sum(sa.high)

result
------
7
6
8
8
8
8
6
8

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-25 : 12:44:57

Do you have a question or a problem?

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2009-11-25 : 12:48:57

See in the result i want 8 only which is the count of tthe survey_id
..... do u get it now??am i being clear enough?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-25 : 12:54:08

remove the "group by" and try like this...

select count(distinct s.survey_id )
from survey s join survey_response sr on s.survey_id = sr.survey_id
join survey_answer sa on sa.answer_id = sr.answer_id
where sum(sa.weight)= sum(sa.high)

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2009-11-26 : 01:48:03

sum is aggregate function man... u cant use this in where

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 02:04:31

Try this

select count(survey_id ) from
(
select s.survey_id
from survey s join survey_response sr on s.survey_id = sr.survey_id
join survey_answer sa on sa.answer_id = sr.answer_id
group by s.survey_id
having sum(sa.weight)= sum(sa.high)
) as t

Madhivanan

Failing to plan is Planning to fail

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2009-11-26 : 02:24:38

Yes that worked ... thnx Madhivanan !!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 02:28:55

quote:
Originally posted by nitsmooth

Yes that worked ... thnx Madhivanan !!

You are welcome

Madhivanan

Failing to plan is Planning to fail

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2009-11-26 : 04:45:43

Hi Madhivanan,
Could u help me here
I have written these two queries

select Assignee, count(callid) Delighted from
(
select s.callid, ag.assignee
from survey s join survey_response sr on s.survey_id = sr.survey_id
join survey_answer sa on sa.answer_id = sr.answer_id
join asgnmnt ag on s.callid = ag.callid
group by s.callid, ag.assignee
having sum(sa.weight)= sum(sa.high)
)D
group by assignee

REsult
------
Assignee Delighted

Nitish Dhar 2
Suzette Strayer 7

select Assignee, count(survey_id)TotalSurvey from
(
select s.survey_id, ag.assignee
from survey s join survey_response sr on s.survey_id = sr.survey_id
join survey_answer sa on sa.answer_id = sr.answer_id
join asgnmnt ag on s.callid = ag.callid
group by s.survey_id, ag.assignee
)T
group by assignee

Result
-------

Assignee TotalSurvey

Nitish Dhar 6
Suzette Strayer 18

No i want to combine the two results and find the percentage
Something like this:

Assignee TotalSurvey Delighted PercentageDelighted
Nitish Dhar 2 6 33%
Suzette Strayer 7 18 38%

How do i do this ???

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 05:46:35

select Assignee,
count(case when weight=high then callid end) as Delighted,
count(survey_id) as TotalSurvey ,
count(case when weight=high then callid end)*100.0/count(survey_id) as PercentageDelighted
from
(
select s.callid, ag.assignee,sum(sa.weight) as weight,sum(sa.high) as high
from survey s join survey_response sr on s.survey_id = sr.survey_id
join survey_answer sa on sa.answer_id = sr.answer_id
join asgnmnt ag on s.callid = ag.callid
group by s.callid, ag.assignee
) T
group by assignee

Madhivanan

Failing to plan is Planning to fail

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2009-11-26 : 05:59:12

Worked like charm !!! Thanks Madi