Click here to Skip to main content
15,884,298 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all am writing query to fetch data for previous month and current month from same table.
but my query is giving error

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 13 Column: 29


If i use that commented line its throwing that error actuall iwant to connect
ecoll_lodg_prod_code
with base table.

What I have tried:

select 
1 user_id,
t1.ecoll_cust_cr_acc_no  "ACCOUNT_NUMBER",
(case when t1.ecoll_lodg_prod_code='ENEFT' then 'NEFT'
when t1.ecoll_lodg_prod_code='RTGS' then 'RTGS'
when t1.ecoll_lodg_prod_code='IMPS' then 'IMPS'
when t1.ecoll_lodg_prod_code='ERTGS' then 'RTGS'
when t1.ecoll_lodg_prod_code='EFT' then 'FT'
when t1.ecoll_lodg_prod_code='NEFT' then 'NEFT' end) "COLLECTION_MODE",

(select sum(t2.ecoll_lodg_tran_amt) from ECOLL_LODG_VOL t2 where t2.ecoll_cust_cr_acc_no=t1.ecoll_cust_cr_acc_no 
and (trunc(t2.ecoll_lodg_date_time) between Last_Day(ADD_MONTHS(sysdate,-2))+1 and Last_Day(ADD_MONTHS(sysdate,-1)))
and t2.ecoll_lodg_prod_code=t1.ecoll_lodg_prod_code
) "PREVIOUSVALUE",

(select sum(ecoll_lodg_tran_amt) from ECOLL_LODG_VOL t where t.ecoll_cust_cr_acc_no=t1.ecoll_cust_cr_acc_no 
and  trunc(t.ecoll_lodg_date_time) BETWEEN TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE,-1))+ 1)AND TRUNC(SYSDATE)
--and t.ecoll_lodg_prod_code=t1.ecoll_lodg_prod_code
)
"CURRENTVALUE",

(select count(t3.ecoll_lodg_tran_amt) from ECOLL_LODG_VOL t3 where t3.ecoll_cust_cr_acc_no=t1.ecoll_cust_cr_acc_no 
and (trunc(t3.ecoll_lodg_date_time) between Last_Day(ADD_MONTHS(sysdate,-2))+1 and Last_Day(ADD_MONTHS(sysdate,-1)))
-- and t3.ecoll_lodg_prod_code=t1.ecoll_lodg_prod_code
) "PREVIOUSVOLUME",

(select count(t4.ecoll_lodg_tran_amt) from ECOLL_LODG_VOL t4 where t4.ecoll_cust_cr_acc_no=t1.ecoll_cust_cr_acc_no 
and  trunc(t4.ecoll_lodg_date_time) BETWEEN TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE,-1))+ 1)AND TRUNC(SYSDATE)
--and t4.ecoll_lodg_prod_code=t1.ecoll_lodg_prod_code
)"CURRENTVOLUME"

from 
ECOLL_LODG_VOL t1 where t1.ecoll_cust_cr_acc_no='111111111111111'
group by
1,
t1.ecoll_cust_cr_acc_no  ,
(case when t1.ecoll_lodg_prod_code='ENEFT' then 'NEFT'
when t1.ecoll_lodg_prod_code='RTGS' then 'RTGS'
when t1.ecoll_lodg_prod_code='IMPS' then 'IMPS'
when t1.ecoll_lodg_prod_code='ERTGS' then 'RTGS'
when t1.ecoll_lodg_prod_code='EFT' then 'FT'
when t1.ecoll_lodg_prod_code='NEFT' then 'NEFT' end);
Posted
Updated 10-Feb-20 1:39am

You have group by 1 in that last query, but the 1st output from the query is a count - you can't group by a count
 
Share this answer
 
Comments
Maciej Los 10-Feb-20 7:37am    
5ed!
In addition to solution #1 by CHill60, please read this: Oracle / PLSQL: ORA-00979 Error Message[^]

BTW: Sorry, but your query is ugly. To many subqueries is in use. I'd receommend to read about JOIN's[^]. This might be helpful too: Visual Representation of SQL Joins[^]
 
Share this answer
 
Comments
Member 11337367 2-Mar-20 5:06am    
How about this..?
select 1 user_id,
t1.ecoll_cust_cr_acc_no "ACCOUNT_NUMBER",t1.ecoll_lodg_prod_code,sum(prv.ecoll_lodg_tran_amt),
count(prv.ecoll_lodg_tran_amt),sum(cur.ecoll_lodg_tran_amt),
count(cur.ecoll_lodg_tran_amt)
from ECOLL_LODG_VOL t1
join ECOLL_LODG_VOL prv on prv.ecoll_cust_cr_acc_no=t1.ecoll_cust_cr_acc_no and (trunc(prv.ecoll_lodg_date_time)
between Last_Day(ADD_MONTHS(sysdate,-2))+1 and Last_Day(ADD_MONTHS(sysdate,-1))) and prv.ecoll_lodg_prod_code=t1.ecoll_lodg_prod_code
join ECOLL_LODG_VOL cur on cur.ecoll_cust_cr_acc_no=t1.ecoll_cust_cr_acc_no
and trunc(cur.ecoll_lodg_date_time) BETWEEN TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE,-1))+ 1)AND TRUNC(SYSDATE)
and prv.ecoll_lodg_prod_code=t1.ecoll_lodg_prod_code
group by t1.ecoll_cust_cr_acc_no,'1',t1.ecoll_lodg_prod_code

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900