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;

The problem is that you are trying to group by an aggregate: `sum(amount)`. Do you want to sum amount, or group by it? Also, you are not grouping by your `payment_type` field, nor is it being aggregated by a formula. It should go in your GROUP BY. — JNevill, Feb 20 '18 at 21:17
Thanks for your feedback. I am trying to sum the amount of both charge types CRE1 and CRE2 and display the amount in a single row — ComputersAreNeat, Feb 20 '18 at 21:21
Also, you will need to include `case when (charge_type in ('CRE1', 'CRE2') then 'electronic payment' else 'cash'` in the group by clause. (Or group by `charge_type`, but I don't think that is what you want. — Shannon Severance, Feb 20 '18 at 21:21

score 1 · Accepted Answer · answered Feb 20 '18 at 21:27

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.

Thanks! This helps! However I am learning that you can't use aliases in a group-by clause. — ComputersAreNeat, Feb 21 '18 at 00:16
I think I figured it out. I added the entire case statement minus the alias to the group by in parenthesis and it works! I will edit the original post with the final result. Thanks again! — ComputersAreNeat, Feb 21 '18 at 00:33

score 0 · Answer 2 · answered Feb 20 '18 at 21:38

Aggregation functions don't belong in the GROUP BY.

You can fix your problem by looking at just the first three letters of the charge_type:

select acct_no, month, sum(amount), substr(charge_type, 1, 3),
       (case when substr(charge_type, 1, 3) = 'CRE'
             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)
having sum(amount) <> 0
order by acct_no asc;

Group by substr in Oracle

2 Answers2