Multiple GROUP BY possible? - 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 GROUP BY possible?

Author

Topic

mrgr8avill
Starting Member

16 Posts

Posted - 2009-08-17 : 07:04:20

Hi all -- thank for taking the time to read this. I am an idiot trying desperately to upgrade my status to fool, and need tutelage with the following table:

table stats(cityname varchar(50), surveyDate datetime, monthsBack tinyint, statCat varchar(20), lowListPrice int, highListPrice int, numProps smallint)

Los Angeles,8/15/2009,3,Active,100000,200000,100
Los Angeles,8/15/2009,3,Active,200000,300000,200
Los Angeles,8/15/2009,3,All_Active,100000,300000,300
Los Angeles,8/15/2009,1,Active,100000,200000,50
Los Angeles,8/15/2009,3,Sold,100000,200000,20
...

WHAT HAPPENS:
the data are segregated by City, months back, and price range. If more than one price range exists for the sample period, then an aggregate row is added with the "All" prefix and aggregate numbers (see 3 months back in the able above).

If, as in the case of 1 month back in the above table, there is only one price range, then no aggregate "All" row is added.

I need to be able to append those single-row instances to "All_" to indicate that they represent the only row for that city.

In short, I need to be able to change the line in the above table from:

Los Angeles,8/15/2009,1,Active,100000,200000,50

to:

Los Angeles,8/15/2009,1,All_Active,100000,200000,50

The only way I know how to do that is convoluted as hell.

1) create @table1 with distinct city names
2) create @table2 with distinct monthsback
3) create @table3 with distinct statcats
4) do a three-level deep nested WHILE iteration to test whether or not an ALL_should be added

I am hoping there might be some way to use GROUP BY to detect that only one row exists for Active,8/15/2009,1 month back

Hope I am explaining myself adequately, and I thank you in advance for any help or information you can provide.

Sachin.Nand

2937 Posts

Posted - 2009-08-17 : 07:33:55

Is it this?


declare @t as table
(cityname varchar(50), surveyDate datetime, 
monthsBack tinyint, statCat varchar(20), lowListPrice int, highListPrice int, numProps smallint)
insert into @t
select 
'Los Angeles','8/15/2009',3,'Active',100000,200000,100 union all
select 'Los Angeles','8/15/2009',3,'Active',200000,300000,200 union all
select'Los Angeles','8/15/2009',1,'Active',100000,200000,50 union all
select'Los Angeles','8/15/2009',3,'Sold',100000,200000,20

select * from @t
select cityname,surveyDate,monthsBack,'All_' + statCat as Cat, MIN(lowListPrice)as lowListPrice,MAX(highListPrice)as highListPrice
,SUM(numProps)as numProps
 from @t
 group by cityname,monthsBack,statCat,surveyDate

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources