Using SELECT CASE in conjunction with GROUP BY

[Lftsk]

I have an SQL Statement:

SELECT a.SERVER, a.OFFICE, a.LOCATION, a.[Office Name],
a.[GL Code],
RecType = CASE a.AcctBal
WHEN >= 0 THEN 'D'
WHEN < 0 THEN 'C'
END,
a.CLOSEDATE,
0 AS BegTotBal,
0 AS BegRecBal,
SUM(a.AcctBal)
FROM tblAgings a
GROUP BY a.SERVER, a.OFFICE, a.LOCATION, a.[Office Name],
a.[GL Code], a.CLOSEDATE

I'm getting an error saying saying Incorrect syntax near the &quot;>&quot; sign.

I can't Group By the RecType column, I get the same error.

Is there a way to use CASE with GROUP BY in the manner above?

Any help would be appreciated.

 

[tlbroadbent]

You must use the Case function in the Group By Clause. Change the format of the Case function as follows.
[tt]
SELECT
a.SERVER, a.OFFICE, a.LOCATION,
a.[Office Name], a.[GL Code],
RecType =
CASE
WHEN a.AcctBal >= 0 THEN 'D'
ELSE 'C'
END,
a.CLOSEDATE,
0 AS BegTotBal,
0 AS BegRecBal,
AcctBal=SUM(a.AcctBal)
FROM tblAgings a
GROUP BY
a.SERVER, a.OFFICE, a.LOCATION,
a.[Office Name], a.[GL Code],
a.CLOSEDATE,
CASE
WHEN a.AcctBal >= 0 THEN 'D'
ELSE 'C'
END[/tt] If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Lftsk]

Thank You. Once Again, you've solved my problem.

Thanks again.