Multiple Select and 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

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple Select and Group By

Author

Topic

sanj
Starting Member

22 Posts

Posted - 2011-01-20 : 23:13:35

Hi,

I have the following but there seems to something wrong as the data output is the same for all companyname, basically I would like to return the count for each type for each company.


SELECT companyname,
(select count(id) from tbl_data where agreement='Yes') As Agreed,
(select count(id) from tbl_data where agreement='Rejected') As Rejected,
(select count(id) from tbl_data where agreement='No') As Awaiting FROM tbl_data 
Group By CompanyName
Order By CompanyName


Thanks for any help.

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-01-20 : 23:47:59

create table #t(id int,cname varchar(10),agreement varchar(10))
insert into #t
select 1,'tcs','yes' union all
select 2,'hp','yes' union all
select 3,'tcs','no' union all
select 4,'hp','no' union all
select 5,'tcs','rejected' union all
select 6,'tcs','yes' union all
select 7,'hp','rejected'

select * from #t

select distinct t.cname,agreed,notagreed,rejected from #t t
join (select cname, count(agreement) as agreed from #t where agreement='yes' group by cname)t1 on t1.cname=t.cname
join (select cname, count(agreement) as notagreed from #t where agreement='no' group by cname)t2 on t2.cname=t.cname
join (select cname, count(agreement) as rejected from #t where agreement='rejected' group by cname)t3 on t3.cname=t.cname

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2011-01-21 : 06:57:41

try this one
SELECT companyname,
sum(case when agreement = 'Yes' then 1 else 0 end)as Agreed,
sum(case when agreement = 'Rejected' then 1 else 0 end)as Rejected,
sum(case when agreement = 'No' then 1 else 0 end)as Awaiting
FROM tbl_data
Group By CompanyName
Order By CompanyName

sanj
Starting Member

22 Posts

Posted - 2011-01-21 : 09:36:47

wow bklr, that's nice and clean, thanks for your help!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2011-01-25 : 04:03:37

quote:
Originally posted by sanj

wow bklr, that's nice and clean, thanks for your help!

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources