Home » SQL & PL/SQL » SQL & PL/SQL » Get the value by dept (Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production)
Get the value by dept [message #345190] Tue, 02 September 2008 09:57 Go to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi All,

Good day.

I have an issue doing sum value by dept name.

C- Credit
D – Debit

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> desc decode_sign;
 Name                            Null?    Type
 ------------------------------- -------- ----
 DEP_NAME                                 VARCHAR2(20)
 ACC_TYPE                        NOT NULL CHAR(1)
 AMT                                      NUMBER(10,2)

SQL> select * from decode_sign;

DEP_NAME             A       AMT
-------------------- - ---------
HR                   C      2500
HR                   D       200
SOURCE               D      2103
SOURCE               D      1023
SOURCE               C      8956



I have tried below steps

SQL>  select sum((decode(acc_type,'C',Sum(amt),0)) - (decode(acc_type,'D',sum((amt)),0))) as total
  2   from decode_sign
  3   GROUP BY  acc_type;

    TOTAL
---------
     8130


Whereas tring to sum values by get detp_name. Getting the error below


SQL> select dep_name, sum((decode(acc_type,'C',Sum(amt),0)) - (decode(acc_type,'D',sum((amt)),0))) as total
  2  from decode_sign
  3  GROUP BY  acc_type,dep_name
  4  ;
select dep_name, sum((decode(acc_type,'C',Sum(amt),0)) - (decode(acc_type,'D',sum((amt)),0))) as total
       *
ERROR at line 1:
ORA-00937: not a single-group group function


Let me know if you need any more details.

Please throw some light on this.

Thanks & Regards
Thangam.
Re: Get the value by dept [message #345193 is a reply to message #345190] Tue, 02 September 2008 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements.

Regards
Michel
Re: Get the value by dept [message #345204 is a reply to message #345193] Tue, 02 September 2008 10:45 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Insert query
create table 
decode_sign(DEP_NAME VARCHAR2(20),
ACC_TYPE CHAR(1) not null, AMT NUMBER(10,2));


While inserting vales i have used like below.

insert into decode_sign values(&DEP_NAME,&ACC_TYPE,&AMT);


Let me know if you need any more details.

Thanks & Regards
Thangam
Re: Get the value by dept [message #345209 is a reply to message #345204] Tue, 02 September 2008 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the insert statement that we can reproduce your example.

Regards
Michel
Re: Get the value by dept [message #345211 is a reply to message #345209] Tue, 02 September 2008 10:57 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

INSERT INTO DECODE_SIGN VALUES('HR','C',2500);
INSERT INTO DECODE_SIGN VALUES('HR','D',200);
INSERT INTO DECODE_SIGN VALUES('SOURCE','D',2103);
INSERT INTO DECODE_SIGN VALUES('SOURCE','D',1023);
INSERT INTO DECODE_SIGN VALUES('SOURCE','C',8956);


Thanks & Regards
Thangam
Re: Get the value by dept [message #345214 is a reply to message #345211] Tue, 02 September 2008 11:04 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I will give you a clue. Execute the following statement and see what you get.
SQL>  select min(dep_name) dep_name, sum((decode(acc_type,'C',Sum(amt),0)) - (decode(acc_type,'D',sum((amt)),0))) as total
  2   from decode_sign
  3   GROUP BY  acc_type;

Then try to break it down in small pieces and you will find out why ?

Regards

Raj
Re: Get the value by dept [message #345216 is a reply to message #345211] Tue, 02 September 2008 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks.
What you want to do is to sum amt with a coefficient of 1 if type is C and -1 if type is D, and this for each dep_name.

Regards
Michel
Re: Get the value by dept [message #345221 is a reply to message #345216] Tue, 02 September 2008 11:29 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Output like below
Output like anyone below

HR c=2500
   D=0200 	
------------
     2300
------------

Source C=8956
       D=3126(2103+1023)
--------------
         5830
---------------


dep_name   total
---------  -------
HR         2300
SOURCE     5830




Let me know if i am wrong.


Thanks & Regards
Thangam
Re: Get the value by dept [message #345227 is a reply to message #345221] Tue, 02 September 2008 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are wrong on what?
Just convert my previous post in SQL, element by element.

Regards
Michel
Re: Get the value by dept [message #345230 is a reply to message #345221] Tue, 02 September 2008 12:10 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Try the following code :

SQL> SELECT dep_name, sum(total) FROM 
  2  (SELECT dep_name, acc_type,
  3     (decode(acc_type,'C',Sum(amt),0)) - (decode(acc_type,'D',sum((amt)),0)) as total
  4    FROM decode_sign 
  5     GROUP BY  acc_type, dep_name) IN_Q
  6  GROUP BY IN_Q.dep_name;


The Output will be like :

DEP_NAME             SUM(TOTAL)
-------------------- ----------
HR                         2300
SOURCE                     5830


Hope this helps.
Regards,
Jo
Re: Get the value by dept [message #345235 is a reply to message #345230] Tue, 02 September 2008 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@joicejohn

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


In addition, your query is too complex, no need of inner query and double grouping.

Regards
Michel
Re: Get the value by dept [message #345245 is a reply to message #345235] Tue, 02 September 2008 13:33 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Sorry about that Michel.

But I have a small question regarding this. I read the following in an Oracle Reference book sometime back.

Quote:

To create queries that compare/manipulate one grouping of rows with another grouping of rows, at least one of the groupings must be a view or an "Inline View" created in the FROM clause of the query.



That's what imdurai was having problems right? He was using a nested agrregate function (sum function inside another sum function)...

sum((decode(acc_type,'C',Sum(amt),0))- decode(acc_type,'D',sum((amt)),0))


I only used an Inline View based on the quote above to solve the problem.

Please correct me if I am wrong in my above understandings.

Thanks,
Jo
Re: Get the value by dept [message #345247 is a reply to message #345245] Tue, 02 September 2008 13:40 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
no need of inner query and double grouping.

Quote:
Just convert my previous post in SQL, element by element.


Regards
Michel

Previous Topic: Resume Data
Next Topic: CREATE VIEW
Goto Forum:
  


Current Time: Sun Dec 04 14:44:53 CST 2016

Total time taken to generate the page: 0.07616 seconds