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  |
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 #345211 is a reply to message #345209] |
Tue, 02 September 2008 10:57   |
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   |
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 #345230 is a reply to message #345221] |
Tue, 02 September 2008 12:10   |
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   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
Goto Forum:
Current Time: Tue Feb 11 08:46:15 CST 2025
|