• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

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

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
0
drozeveld
Asked:
drozeveld
  • 7
  • 6
1 Solution
 
slightwv (䄆 Netminder) Commented:
did you add cust_number to the select and group by?


select cust_number, sum(decode(a.dc_cd, 'C',nvl(amt,0)*(-1), 'D',nvl(amt,0), nvl(amt,0))
) amt
  from ar_trn_tp a, ar_trn b
  where a.trn_tp_cd = b.trn_tp_cd
  and b.stat_cd = 'P'
  and b.origin_store = '08'
  and b.post_dt < '&age_date'
  and b.cust_cd = '&cust_cd'
 group by cust_number, b.cust_cd
0
 
drozeveldAuthor 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.

0
 
drozeveldAuthor 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?

0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
slightwv (䄆 Netminder) 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

0
 
slightwv (䄆 Netminder) Commented:
or

select  cust_cd, amt from (
...your select here
)
where amt > 0;
0
 
drozeveldAuthor 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.
0
 
slightwv (䄆 Netminder) Commented:
oops.  my mistake.

make it this:
where amt != 0;
0
 
drozeveldAuthor Commented:
nope, still no change.

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

and neither of those worked either.
0
 
slightwv (䄆 Netminder) Commented:
please post what you have
0
 
drozeveldAuthor 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
0
 
slightwv (䄆 Netminder) 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;
0
 
drozeveldAuthor Commented:
PERFECT!!!!!!!!!!

Thanks you!!!!
0
 
slightwv (䄆 Netminder) Commented:
No problem.  Glad to help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now