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)

group by

Author

Topic

nidabp
Starting Member

15 Posts

Posted - 2011-02-03 : 02:05:28

My Data:
sno name id
1 a 1
2 a 2
3 b 3
4 b 4
5 c 3

I need to get the count of items with name = a and name = b and name = c in the above. Then I need to get the sum of (count of items with name = a and name = b) as name = d.

Can anyone please let me know, how to achieve this.

I used a group by on [name], but I could not get the sum of specific items.

Thanx

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-03 : 02:22:55

Check this out ..

Select Name,Count(SNo)
From MyData
Where Name in ('a','b','c')
group by Name

nidabp
Starting Member

15 Posts

Posted - 2011-02-03 : 03:19:03

Hi,

Thanks for your reply. But I need to get the sum of (counts of items 'a' and 'b').

Thanx

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-03 : 03:58:32

Check it once.....

create table #temp
(
sno int,
name char(1),
id int
)
insert into #temp
select 1, 'a', 1 union all
select 2, 'a', 2 union all
select 3, 'b', 3 union all
select 4, 'b', 4 union all
select 5, 'c', 3
GO

select case when (grouping(name) = 1) then 'd'
else isnull(name, 'unknown')
end as name
,count(name)count
from #temp
where name='a' or name='b'
group by name with rollup
union
select name,count(name)
from #temp
group by name

Result of above query:

name count
a 2
b 2
c 1
d 4

IRK

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-02-03 : 04:33:26

Hi,

see this one also!

create table #a(sno int,name varchar(10),id int)
insert into #a values(1,'a',1)
insert into #a values(2,'a',2)
insert into #a values(3,'b',3)
insert into #a values(4,'b',4)
insert into #a values(5,'c',5)
insert into #a values(6,'c',6)

select sum(counting) as summing, ename
from
(select count(sno) counting,(case when name='c'then 'c'
else 'd'
end )as ename
from #a
group by name) A
group by ename

sathish

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-03 : 04:42:35

quote:
Originally posted by nidabp

Hi,

Thanks for your reply. But I need to get the sum of (counts of items 'a' and 'b').

Thanx

I think sum of the Counts of Items having 'a' and 'b' would be equal to all rows having a and b ?

and if so then following is what you are looking for..

Select Count(SNo)
From MyData
Where Name in ('a','b')
?

nidabp
Starting Member

15 Posts

Posted - 2011-02-03 : 05:05:34

Thanks for all the replies..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources