Group by substr in Oracle

Question

Below is an example query:

select acct_no, month, sum(amount), substr(charge_type, 1, 3),
       case when (charge_type in ('CRE1', 'CRE2')
            then 'electronic payment'
            else 'cash'
       end as 'payment_type'
from   billing_data
where  charge_type in ('CRE1', 'CRE2', 'CASH')
group  by acct_no, month, sum(amount), 
          substr(charge_type, 1, 3)
having sum(amount) != 0
order  by acct_no asc;

What I am trying to achieve is to return the sum of the CRE1 and CRE2 charge type amounts grouped together for each account number, where that sum is not 0.

Without the substr in the group by, the query runs and returns the expected results except the CRE1 and CRE2 charge types are not summed together in one row.

When I add the substr in the group by, I get the following error message:

[Error] Execution (63: 15): ORA-00979: not a GROUP BY expression

Is there a way to achieve this in Oracle?

Edit: for anyone who may come across this post. The solution is as follows:

select acct_no, month, sum(amount) as sumofamount, 
       substr(charge_type, 1, 3) as charge_type_substring,
       (
       case when (charge_type in ('CRE1', 'CRE2')
            then 'electronic payment'
            else 'cash'
       end) as payment_type
from   billing_data
where  charge_type in ('CRE1', 'CRE2', 'CASH')
group  by acct_no, month, substr(charge_type, 1, 3), 
       (
       case when (charge_type in ('CRE1', 'CRE2')
            then 'electronic payment'
            else 'cash'
       end)
       having sum(amount) != 0
order  by acct_no asc;

Answer 1

I believe you are going for something like this:

select acct_no, month, sum(amount) as sumofamount, substr(charge_type, 1, 3) as charge_type_substring,
       case when (charge_type in ('CRE1', 'CRE2')
            then 'electronic payment'
            else 'cash'
       end as 'payment_type'
from   billing_data
where  charge_type in ('CRE1', 'CRE2', 'CASH')
group  by acct_no, month, charge_type_substring, payment_type
having sum(amount) != 0
order  by acct_no asc;

I took some liberties with your column aliases. The big take-away here is that sum() doesn't belong in your group by since we are aggregating that column with a formula, but the alias for your CASE statement DOES belong in your group by since it's not being aggregated by a formula.