Problem with GROUP BY syntax and expression

I am really struggling with this code and would appreciate knowing how
to group by the expression (constants) in the SELECT clause:
DECLARE @.LO INT
DECLARE @.HI INT
DECLARE @.StartDate varchar(10)
DECLARE @.EndDate varchar(10)
SELECT @.StartDate = '01/01/2005'
SELECT @.EndDate = '06/30/2005'
SELECT @.LO = 250
SELECT @.HI = 333
SELECT
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDate
, JumboAmount = SUM(JumboAmount)
, JumboMortgages = SUM(JumboMortgages)
, JumboFIXMortgages = SUM(JumboFIXMortgages)
, JumboFIXAmount = SUM(JumboFIXAmount)
, JumboARMMortgages = SUM(JumboARMMortgages)
, JumboARMAmount = SUM(JumboARMAmount)
FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
Z.ZipCode
GROUP BY
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDateRemove the aliases from the GROUP BY.
GROUP BY
StateCD
,CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
,'JumboRange:' + Convert(varchar(4),@.LO) + '-' + Convert(varchar(4),@.HI)
,@.StartDate
,@.EndDate
"JJA" <johna@.cbmiweb.com> wrote in message
news:1123780031.845579.256660@.o13g2000cwo.googlegroups.com...
> I am really struggling with this code and would appreciate knowing how
> to group by the expression (constants) in the SELECT clause:
> DECLARE @.LO INT
> DECLARE @.HI INT
> DECLARE @.StartDate varchar(10)
> DECLARE @.EndDate varchar(10)
> SELECT @.StartDate = '01/01/2005'
> SELECT @.EndDate = '06/30/2005'
> SELECT @.LO = 250
> SELECT @.HI = 333
> SELECT
> StateCD
> , CountyCD
> , Zip
> , Z.CityName
> , Z.StateCode
> , Z.CountyName
> , 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
> Convert(varchar(4),@.HI)
> , 'StartingDate' = @.StartDate
> , 'ThruDate' = @.EndDate
> , JumboAmount = SUM(JumboAmount)
> , JumboMortgages = SUM(JumboMortgages)
> , JumboFIXMortgages = SUM(JumboFIXMortgages)
> , JumboFIXAmount = SUM(JumboFIXAmount)
> , JumboARMMortgages = SUM(JumboARMMortgages)
> , JumboARMAmount = SUM(JumboARMAmount)
> FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
> ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
> Z.ZipCode
> GROUP BY
> StateCD
> , CountyCD
> , Zip
> , Z.CityName
> , Z.StateCode
> , Z.CountyName
> , 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
> Convert(varchar(4),@.HI)
> , 'StartingDate' = @.StartDate
> , 'ThruDate' = @.EndDate
>|||I would use a table expression, like this:
create table t(i int, d money)
insert into t values(1, 1.00)
insert into t values(1, 2.00)
insert into t values(2, 3.00)
insert into t values(2, 4.00)
select t.*, 'Criteria' = 'Some text here'
from (select i, sum(d) sumd from t group by i) t
drop table t
Besides, what's the point of grouping by both CountyCD and CountyName?
If you group by only by StateCD and CountyCD, the query might run much
faster. The rest columns could be retrieved after grouping by, like
this:
create table t(i int, d money)
insert into t values(1, 1.00)
insert into t values(1, 2.00)
insert into t values(2, 3.00)
insert into t values(2, 4.00)
create table s(i int, sname char(5))
insert into s values(1,'One')
insert into s values(2,'Two')
select s.sname, t.sumd, 'Criteria' = 'Some text here'
from (select i, sum(d) sumd from t group by i) t
join s on s.i=t.i
sname sumd Criteria
-- -- --
One 3.0000 Some text here
Two 7.0000 Some text here
drop table t
drop table s|||Thank you so much. Great idea...I now have it working per your
suggestion.