Help needed for Grouping & Count

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

SQL Server 2008 Forums

Transact-SQL (2008)

Help needed for Grouping & Count

Author

Topic

Mits
Starting Member

48 Posts

Posted - 2011-11-16 : 14:21:19

Hi all,

My mind has just gone blank and would like little help from expert out there.

Following is my schema

Table: Advert

Advertid int

Table:AdvertResponse

ResponseID-----AdvertID-----QualityId
1--------------1000---------2
2--------------1000---------5
3--------------1000---------8
4--------------1000---------2
5--------------1000---------7
6--------------1000---------4
7--------------1000---------5
8--------------1000---------5
9--------------1000---------8

Qualityid-------QualityText

2---------------Good
4---------------Fair
5---------------Average
7---------------Poor
8---------------Not-Applicable

I would like to display the information in following format.

AdvertId---QualityText
1000------Good--------Fair----Average--Poor----Not-Applicable---Total
----------2-----------1-------3--------1-------2-----------------9

Any help would be highly appreciated.

Regards

Mitesh

singularity
Posting Yak Master

153 Posts

Posted - 2011-11-16 : 19:24:14

[code]
select a.AdvertID,
count(case when b.QualityText = 'Good' then a.ResponseID end) as Good,
count(case when b.QualityText = 'Fair' then a.ResponseID end) as Fair,
count(case when b.QualityText = 'Average' then a.ResponseID end) as Average,
count(case when b.QualityText = 'Poor' then a.ResponseID end) as Poor,
count(case when b.QualityText = 'Not-Applicable' then a.ResponseID end) as Not_Applicable,
count(a.ResponseID) as Total
from Advert a
join QualityDesc b on a.QualityID = b.QualityID
group by a.AdvertID
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 00:22:42

another way is to use pivot


select AdvertID,
[Good],[Fair],[Average],[Poor],[Not_Applicable],
[Good]+[Fair]+[Average]+[Poor]+[Not_Applicable] AS Total
from
(
select *
from Advert a
join QualityDesc q on a.QualityID = q.QualityID
)t 
pivot (count(ResponseID) FOR QualityText IN ([Good],[Fair],[Average],[Poor],[Not_Applicable]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources