How to SUM a CASE statement

[wfd1bdb]

I need to be able to sum a CASE statement and put that sum in a field [Charges]. Below is the code I have tried. It does not work and I get the following error.

"Column 'A.CHGALLOWED' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Now if I add 'A.CHGALLOWED' to the GROUP BY clause then I get 2 or more lines in the outcome of the report. I don't want that. I need 1 line per entry. I guess I do not understand how to put 'A.CHGALLOWED' in the AGGREGATE function, since putting it in the GROUP BY function does not yield the results I want.
Thanks in advance for any help you might have.

SELECT
A.COMPANY ,A.PROVIDER,A.FACILITY,A.CPTPRINT,A.PROCDESC
,B.NAME2 ,B.GROUPCODE
,CASE WHEN a.cptPRINT in ('T1016HWAJ',
'H2011HWAJ',
'H0033HWAJ',
'T1016HWTGAJ')
THEN a.chgallowed ELSE a.chgamount END
as [Amount]
,SUM(A.CUNITS) AS [UNITS]
,SUM(A.CHGallowed) AS [CHARGES]
...
...
GROUP BY
A.COMPANY
,A.CPTPRINT
,A.PROCDESC
,A.PROVIDER
,B.NAME2
,A.FACILITY
,B.GROUPCODE
ORDER BY
A.PROVIDER,
A.CPTPRINT

 

[bborissov]

You want to sum it right?
Then do:

SELECT A.COMPANY ,A.PROVIDER,A.FACILITY,A.CPTPRINT
       , A.PROCDESC,B.NAME2 ,B.GROUPCODE ,
       , SUM(CASE WHEN a.cptPRINT in ('T1016HWAJ',
                                      'H2011HWAJ',
                                      'H0033HWAJ',
                                      'T1016HWTGAJ')
                 THEN a.chgallowed ELSE a.chgamount END)
       as [Amount]
      , SUM(A.CUNITS) AS [UNITS]
      , SUM(A.CHGallowed) AS [CHARGES]
...
...
GROUP BY A.COMPANY, A.CPTPRINT, A.PROCDESC, A.PROVIDER
        ,B.NAME2, A.FACILITY, B.GROUPCODE
ORDER BY A.PROVIDER, A.CPTPRINT

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[wfd1bdb]

bborissov,

Thank you...I was thinking along those lines and tried what you suggested but a simple syntax error prevented it from being successful! Thanks for catching it!