SQL: Convert and calculate amount into negative depending on value in another column

Question

I'm attempting to write a script that will return the chk_amount totals per company and a grand total. The problem I'm having is if the check is void then return it as a credit amount, if not return it as is. All values in chk_amount are positive.

I'm getting an error that void is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Any suggestions? All I would want on the output is company_name and amount.

Here's the code;

SELECT company_name = CASE GROUPING(company_name) WHEN 1 THEN 'Total' ELSE company_name END,
    CASE WHEN void = 'Y'
            THEN FORMAT(sum(chk_amount * (-1)), 'C', 'en-us')
             ELSE FORMAT(sum(chk_amount), 'C', 'en-us')
              END AS amount
from payments

INNER JOIN company_list on payments.company_no = company_list.company_id

where CONVERT( varchar(10),date_last_modified,101) = '1/13/2016'

Group by ROLLUP(company_name)

Thank you in advance!

Answer 1

You will need to add void to the group by in this way -> Group by ROLLUP(company_name, void)