Grouping with Count - 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 2000 Forums

Transact-SQL (2000)

Grouping with Count

Author

Topic

cindylee
Yak Posting Veteran

55 Posts

Posted - 2006-08-15 : 01:03:04

Hi Guys
I need some help in grouping function. ihave a table with following fields:

Customer product1 product2 product3
a 1 1 5
b 1 1 1
c 1 5 6
d 5 6 6
e 1 1 1
a 5 5 5

Now i need to group on the customer (customer a,b, c called South and the total count of the products and other customers as north and the total product count)

cutomer product1 product2 product3

South 4 4 4
North 2 2 2

I currently doin it using a temp table? Any other method/suggestion
much appreciated.

Thanks

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-15 : 01:50:57

[code]
Select A.* From
(Select Case When customer In ('a','b','c') Then 'South' Else 'North' End As Customer,
Count(Product1) Product1,Count(Product2) Product2 ,Count(Product3) Product3
From TableName
Group by
Case When customer In ('a','b','c') Then 'South' Else 'North' End
) as A
[/code]

Chirag

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-16 : 03:52:39

or more directly....

declare @xyz table
(
	customer varchar(10),
	product1 int,
	product2 int,
	product3 int
)

insert into @xyz
select 'a', 1, 1, 5 union all
select 'b', 1,1,1  union all
select 'c',1,5,6 union all
select 'd', 5,6,6  union all
select 'e',1,1,1 union all
select 'a', 5,5,5


select (case when customer in ('a', 'b', 'c') then 'South' else 'North' end) as Customer,
	count(Product1) as Product1, count(Product2) as Product2, count(Product3) as Product3
from @xyz
group by (case when customer in ('a', 'b', 'c') then 'South' else 'North' end)

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources