>>How do I GROUP BY an aggregate column in MS SQL?
>>
>>I'm doing SPT
>
>You could use a derived table.
>Could you post the DDL of the table being queried and a little sample data to show what you're trying to achieve?
>Thx
I think this is what you mean as the DDL:

CREATE TABLE [dbo].[debtor] (
	[debtor] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[client] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[name1] [char] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[name2] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[address] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[address_2] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[city] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[st] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[zip] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[packet_nbr] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[phone_res] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[dob] [datetime] NULL ,
	[dsk] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[clientref] [char] (19) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[lst_pay] [datetime] NULL ,
	[dt_assign] [datetime] NULL ,
	[prin_asgd] [numeric](12, 2) NOT NULL ,
	[prin_col] [numeric](12, 2) NOT NULL ,
	[ssn] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[last_act] [datetime] NULL ,
	[st_cd] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cancel_amt] [numeric](12, 2) NOT NULL ,
	[setup_dt] [datetime] NULL ,
	[drive_lic] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ltr_series] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[sp_fld1] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[sp_fld2] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[sp_fld3] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[sp_fld4] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[sp_fld5] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[sp_fld6] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[com_earn] [numeric](12, 2) NOT NULL ,
	[prin_adj] [numeric](12, 2) NOT NULL ,
	[payplan] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cancel_dt] [datetime] NULL ,
	[rdsk] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[judgement] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[comm_rt] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[history_dt] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[nsf_date] [datetime] NULL 
) ON [PRIMARY]

Here is the VFP6 query I'm attempting to duplicate:

	sele ;
	client,;
	dsk,;
	dt_assign,;
	subs(dtoc(dt_assign),1,2)+subs(dtoc(dt_assign),7,2) as mmyy,;
	subs(dtoc(dt_assign),7,2)as yy, ;
	subs(dtoc(dt_assign),1,2)as mm,;
	iif(prin_asgd<500,1,iif(prin_asgd>499 and prin_asgd<10000,2,iif(prin_asgd>9999,3,0))) as br1,;
	count(debtor) as an,;
	sum(prin_asgd) as aa,;
	sum(prin_col) as pa,;
	sum(com_earn) as ca,;
	sum(prin_col)/sum(prin_asgd) as pp;
	from debtor;
	where (client='MCSCARMS' or client='MCSMEGA') and (dt_assign>btdt);
	order by client, br1, yy, mm, dsk;
	group by client, br1, mmyy, dsk;

...And here's what I've got so far (but it's not grouping right):

SELECT CLIENT, dsk, dt_assign,
	 LEFT(CONVERT(char(8),dt_assign,1),2)+SUBSTRING(CONVERT(char(8),dt_assign,1),7,2) AS mmyy,
	 LEFT(CONVERT(char(8),dt_assign,1),2) AS mm,
	 SUBSTRING(CONVERT(char(8),dt_assign,1),7,2) AS yy,
	COUNT(Debtor) AS an, SUM(prin_asgd) AS aa, SUM(prin_col) AS pa,
	SUM(com_earn) AS ca, 
	CASE
		WHEN SUM(prin_asgd) > 0 THEN SUM(prin_col)/SUM(prin_asgd)
		ELSE 0
	END AS pp,
	CASE 
		WHEN prin_asgd <   500 THEN 1
		WHEN prin_asgd < 10000 THEN 2
		WHEN prin_asgd >  9999 THEN 3
		ELSE 0
	END AS br1
FROM [HarvardCollect].[dbo].[debtor]
WHERE dt_assign > '03/05/2003 00:00:00' 
AND (client = 'MCSCARMS' OR client = 'MCSMEGA')
GROUP BY client, 
CASE 
	WHEN prin_asgd <   500 THEN 1
	WHEN prin_asgd < 10000 THEN 2
	WHEN prin_asgd >  9999 THEN 3
	ELSE 0
END,
LEFT(CONVERT(char(8),dt_assign,1),2)+SUBSTRING(CONVERT(char(8),dt_assign,1),7,2),
dt_assign,
dsk,
prin_asgd