Home » SQL & PL/SQL » SQL & PL/SQL » To compute total amount in one SQL statement
To compute total amount in one SQL statement [message #223483] Fri, 09 March 2007 02:42 Go to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

I'm here would like to seek help on how to compute total amount in one SQL statement. I.E:

Table A

emp_no   expenses_code  expenses_amount  Department_id
001       SAL             2000             12
001       OT              20               12
001       INS             12.3             12
001       MED             60               12
002       SAL             4000             15
002       OT              40               15
002       INS             52.3             15
002       MED             140              15
004       SAL             4230             15
004       OT              0                15
004       INS             45               15
004       MED             200              15
004       UP              20               15

What I'm trying to do is get the total expenses amount base on the expenses code and group by emp no.

Please advise....

regards
Ying


[Mod-edit: added code tags]

[Updated on: Fri, 09 March 2007 04:12]

Report message to a moderator

Re: To compute total amount in one SQL statement [message #223484 is a reply to message #223483] Fri, 09 March 2007 02:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please provide an example of your desired output (and use code-tags to format it)
Re: To compute total amount in one SQL statement [message #223485 is a reply to message #223483] Fri, 09 March 2007 02:44 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
I don't quite understand what you mean.
Can you give a example of the desired output?
Re: To compute total amount in one SQL statement [message #223488 is a reply to message #223483] Fri, 09 March 2007 03:03 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
The ouput I want is as below:
emp_no   total_expenses
001       2092.3
002       4232.3
003       4495


We have a table which store employee expenses (salary, overtime $ amount, medical expenses and so on), so we are required to provide a report which can show the total expenses of each employee.

Pls advise and thank so much for the prompt response.

[Mod-edit: added code tags AGAIN]

[Updated on: Fri, 09 March 2007 03:05] by Moderator

Report message to a moderator

Re: To compute total amount in one SQL statement [message #223490 is a reply to message #223488] Fri, 09 March 2007 03:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please read the sticky on how to format your posts. If you submit tabular information, you have to use code-tags to keep it readable.
Re: To compute total amount in one SQL statement [message #223492 is a reply to message #223488] Fri, 09 March 2007 03:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What did you try so far??
Re: To compute total amount in one SQL statement [message #223495 is a reply to message #223488] Fri, 09 March 2007 03:16 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
mmmm this is a quite simple one... so I understand Frank's remark.

however.... I'll give it a try:
select   emp_no,
         sum(expenses_amount)
from     A
group by emp_no
Re: To compute total amount in one SQL statement [message #223504 is a reply to message #223483] Fri, 09 March 2007 04:08 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Sorry, I don't know how to use the code-tags, trying...

well, I think I myself has confused what I want.
I can achieve this by using below statement:
select empno, sum(expenses_amount) from table_A where expenses_code in ('INS','SAL') group by employee_no.
This is because not all the expenses_code are included as total_expenses.

But, I need to further enhance my SQL statement so that I can get the total_expenses base on the department(have add department id column in my table) and get the number of headcount in the department. So the end result is as below:

Department_id  Total_Expenses No_HeadCount
12                 2012.3          1
15                 8327.3          2


pls advise....


[Updated on: Fri, 09 March 2007 04:35]

Report message to a moderator

Re: To compute total amount in one SQL statement [message #223506 is a reply to message #223504] Fri, 09 March 2007 04:09 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Please refer to above update.

[Updated on: Fri, 09 March 2007 04:39]

Report message to a moderator

Re: To compute total amount in one SQL statement [message #223519 is a reply to message #223506] Fri, 09 March 2007 05:38 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
some thing like this?


select department_id 
       ,sum(expenses_amount)
       ,count(distinct empno) 
from table_A 
where expenses_code in ('INS','SAL') 
group by department_id



Re: To compute total amount in one SQL statement [message #223629 is a reply to message #223483] Fri, 09 March 2007 19:24 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

Thank you so much, this is what I want.
I really like orafaq so much, as most of the time I can get solution from here.
Re: To compute total amount in one SQL statement [message #223975 is a reply to message #223504] Mon, 12 March 2007 08:42 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
ying wrote on Fri, 09 March 2007 05:08
Sorry, I don't know how to use the code-tags, trying...




Really now? Then you should first refer to this http://www.orafaq.com/forum/?section=readingposting&t=help_index&66800/#style before posting another message.
Previous Topic: date problem
Next Topic: Regarding buffer overflow error
Goto Forum:
  


Current Time: Thu Dec 08 12:42:45 CST 2016

Total time taken to generate the page: 0.10671 seconds