Group By Help - 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 Help

Author

Topic

orangefan08
Starting Member

10 Posts

Posted - 2010-09-13 : 08:20:39

[i]I have a table that looks something like this...

Customer Name Price

Eric 2
Eric 4
Eric 15
Eric 25
Eric 17
Steve 5
Steve 7
Steve 28
Steve 30

I want the query results to group by Customer Name, and then have 3 buckets for averaging the price( i.e. - averages records with price <=10 together, records with price 10<price<=20 together and finally records with price >20.

The query result should look like this...

Customer Name Price

Eric 3
Eric 16
Eric 25
Steve 6
Steve 29

I've tried a Case statement in the group by clause but can't get it to work.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-13 : 08:45:57

Maybe there exists an more elegant way but it seems to work:

select DISTINCT
Cust,
avg(Price) over (partition by Cust,
                 case 
                  when Price > 20 then 21
                  when Price <= 20 and Price >= 10 then 20
                  when Price <= 10 then 10
                 end) as AVGPrice
from
(
select 'Eric' as Cust, 2 as Price union all
select 'Eric' as Cust,  4 as Price union all
select 'Eric' as Cust,  15 as Price union all
select 'Eric' as Cust,  25 as Price union all
select 'Eric' as Cust,  17 as Price union all
select 'Steve' as Cust,  5 as Price union all
select 'Steve' as Cust,  7 as Price union all
select 'Steve' as Cust,  28 as Price union all
select 'Steve' as Cust,  30 as Price
) testtable

No, you're never too old to Yak'n'Roll if you're too young to die.

orangefan08
Starting Member

10 Posts

Posted - 2010-09-13 : 11:26:12

While that worked, I couldn't adjust it to add more columns. I need another column handled just like CustomerName( called Location) and another column handled like Price (called Quantity).

I tried removing Distint and used the group by but it's not working right. Here is what my code looks like....

select

CustomerName
,Location

,avg(Price) over (partition by CustomerName,Location,
case when convert(int,Price) > 20 then 21
when convert(int,Price) <= 20 and convert(int,Price)>= 10 then 15
when convert(int,Price) < 25 then 24
end) as AVGPrice

,avg(Quantity) over (partition by CustomerName,Location,
Case when convert(int,Quantity) > 10 then 11
when convert(int,Quantity) <= 5 and convert(int,Quantity)>= 10 then 7
when convert(int,Quantity) < 5 then 2
end) as AVGQuantity

from Table1

group by CustomerName, Location,Price, Quantity

This is returning too many records. I appreciate any help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources