CODEWITHSUNDEEP
sqloracle-databasegroup-bysubstrregexp-substr
ComputersAreNeat
ComputersAreNeat

Reputation: 195

Group by substr in Oracle

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;

Upvotes: 2

Views: 13267

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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;

Upvotes: 1

JNevill
JNevill

Reputation: 50200

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.

Upvotes: 2

Related Questions