We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

'ORA-00937: not a single-group group function' question

drozeveld
drozeveld asked
on
Medium Priority
628 Views
Last Modified: 2012-05-11
I have a sequel statement that I would like it to list the customer # in the results and have tried to add to the SELECT line.  When I add to the SELECT line, I receive the ORA-00937 error.  Could someone please advise as how I get the customer # to list in the results.  I tried to add the GROUP BY command, but that did not bring the information back either.

Thanks - Deb

  1  select sum(decode(a.dc_cd, 'C',nvl(amt,0)*(-1), 'D',nvl(amt,0), nvl(amt,0))
) amt
  2  from ar_trn_tp a, ar_trn b
  3  where a.trn_tp_cd = b.trn_tp_cd
  4  and b.stat_cd = 'P'
  5  and b.origin_store = '08'
  6  and b.post_dt < '&age_date'
  7  and b.cust_cd = '&cust_cd'
  8* group by b.cust_cd
live> /
Enter value for age_date: 25-apr-11
old   6: and b.post_dt < '&age_date'
new   6: and b.post_dt < '25-apr-11'
Enter value for cust_cd: 00133667
old   7: and b.cust_cd = '&cust_cd'
new   7: and b.cust_cd = '00133667'
 
    AMT
-------
   -944
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Ok - so I just put cust_cd back in the SELECT line after I had the group by line and it now works like I want it to......

Sorry to waste your time, but yet your response helped me return to my sql statement and try again.

Author

Commented:
Do you know how I would get the customers that have a balance greater than $0.00 to work in this statement?  I have changed it to this:

     1  select cust_cd, sum(decode(a.dc_cd, 'C',nvl(amt,0)*(-1), 'D',nvl(amt,0),
 nvl(amt,0))) amt
     2  from ar_trn_tp a, ar_trn b
     3  where a.trn_tp_cd = b.trn_tp_cd
     4  and b.stat_cd = 'P'
     5  and b.amt > 0
     6  and b.origin_store = '08'
     7  and b.post_dt < '&age_date'
     8  group by b.cust_cd
     9  /


and I am receiving this:

CUST_CD        AMT
---------- -------
00110509         0
00110758         0
00111158         0
00111267         0
00111290         0
00111492         0
00111565         0
00111568         0
00111570         0
00111753         0
00111917         0
00111942         0
00112018     -4.99
00112240        -5
00112288         0
00112559         0


I only want the customer like the 3rd & 4th to last here to show.  What am I missing?

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
add:

...
group by b.cust_cd
having sum(decode(a.dc_cd, 'C',nvl(amt,0)*(-1), 'D',nvl(amt,0),
 nvl(amt,0))) > 0

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
or

select  cust_cd, amt from (
...your select here
)
where amt > 0;

Author

Commented:
What I am trying to accomplish is a list of the customers with a credit balance and when I add the WHERE AMT > 0 line, I receive only the customers with a positive balance.  When I change the operator to <, I receive no customers.

I guess I will just have to work with the list that lists all customers including the ones with the 0 balance.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
oops.  my mistake.

make it this:
where amt != 0;

Author

Commented:
nope, still no change.

I also tried
where amt ! = 0    and
where amt! = 0

and neither of those worked either.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
please post what you have

Author

Commented:
1  select cust_cd, sum(decode(a.dc_cd, 'C',nvl(amt,0)*(-1), 'D',nvl(amt,0), nv
l(amt,0))) amt
  2  from ar_trn_tp a, ar_trn b
  3  where amt! = 0
  4  and a.trn_tp_cd = b.trn_tp_cd
  5  and b.stat_cd = 'P'
  6  and b.origin_store = '08'
  7  and b.post_dt < '&age_date'
  8* group by b.cust_cd
live> save custbalbyst repl
Wrote file custbalbyst.sql
live> /
Enter value for age_date: 25-apr-11
old   7: and b.post_dt < '&age_date'
new   7: and b.post_dt < '25-apr-11'
 
CUST_CD        AMT
---------- -------
00110509         0
00110758         0
00111158         0
00111267         0
00111290         0
00111492         0
00111565         0
00111568         0
00111570         0
00111753         0
00111917         0
00111942         0
00112018     -4.99
00112240        -5
00112288         0
00112559         0
00112638         0
00112796         0
00112913         0
00113005         0
00113133         0
 
CUST_CD        AMT
---------- -------
00113281         0
00113651         0
00113774         0
00114000         0
00114348         0
00114477         0
00114502         0
00114772         0
00114792         0
00115033         0
00115138         0
00115225         0
00115310         0
00115404         0
00115584         0
00115786     -1.45
00115880         0
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
select cust_cd, amt from (
select cust_cd, sum(decode(a.dc_cd, 'C',nvl(amt,0)*(-1), 'D',nvl(amt,0), nvll(amt,0))) amt
  from ar_trn_tp a, ar_trn b
  where amt! = 0
  and a.trn_tp_cd = b.trn_tp_cd
  and b.stat_cd = 'P'
  and b.origin_store = '08'
  and b.post_dt < '&age_date'
  group by b.cust_cd
) where amt != 0;

Author

Commented:
PERFECT!!!!!!!!!!

Thanks you!!!!
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
No problem.  Glad to help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.