Home » SQL & PL/SQL » SQL & PL/SQL » trying to find solution! (10g)
trying to find solution! [message #347145] Wed, 10 September 2008 19:31 Go to next message
ukdas
Messages: 32
Registered: September 2008
Location: London
Member

hi guys, i need a solution of a query.
i have the following information about the tables as:
agent table.. contains agent_id pk, location_id, first_name, last_name and other
location table contains location_id and description
agent_salary table contains agent_id, salary, and other tables

and my question is like :
listing the locations using agent_salary table and all agents' salaries for each location where the total amount should be more than 300.
i have written the following syntax which filter out the locations and corresponding agents' salary which is more than 300.

select
l.description locations,
ags.salary
from
locations l
inner join agents a on
a.location_id=l.location_id
inner join agent_salary ags on
ags.agent_id=a.agent_id
group by
l.description,
ags.salary

HAVING
ags.salary>300
;

but there are some locations where more than one agent working and on those case the total salary would be the total of those agents belong to the particular location.
so, i may require to do further work which i cann't really find the way to proceed right now.

if anybody can help me solving this problem would much be appreciated.
thanks.
Re: trying to find solution! [message #347146 is a reply to message #347145] Wed, 10 September 2008 19:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Re: trying to find solution! [message #347195 is a reply to message #347145] Thu, 11 September 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What a meaningful and useful title!

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: trying to find solution! [message #347218 is a reply to message #347145] Thu, 11 September 2008 01:54 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> but there are some locations where more than one agent working and on those case the total salary would be the total of those agents belong to the particular location.

so why do you group by salary and do not count its sum?
by the way, if agents in one location have different salary, you will get two rows for them in the result set.
Re: trying to find solution! [message #347280 is a reply to message #347145] Thu, 11 September 2008 04:22 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

As per my unserstanding you need something like this

create table agent
(agent_id number primary key,
location_id number,
first_name varchar2(20),
last_name varchar2(20));


create table location
(location_id number primary key,
description varchar2(20));

create table agent_salary
(agent_id number ,
salary number );


insert into agent values (100,1,'h','r');
insert into agent values (102,2,'a','b');
insert into agent values (103,1,'c','d');
insert into agent values (104,2,'e','f');
insert into agent values (105,3,'g','h');
insert into agent values (106,4,'i','j');
insert into agent values (107,5,'x','y');

insert into location values (1,'INDIA');
insert into location values (2,'USA');
insert into location values (3,'FRANCE');
insert into location values (4,'CANADA');
insert into location values (5,'UK');

insert into agent_salary values (100,5000);
insert into agent_salary values (102,6000);
insert into agent_salary values (103,7000);
insert into agent_salary values (104,100);
insert into agent_salary values (105,500);
insert into agent_salary values (106,600);
insert into agent_salary values (107,800);

commit;

SQL> select description descr,
2 sum(salary) sal
3 from location l,
4 agent_salary ags,
5 agent a
6 where a.agent_id = ags.agent_id
7 and a.location_id = l.location_id
8 and ags.salary>300
9 group by description;

DESCR SAL
-------------------- ----------
FRANCE 1100
INDIA 12000
UK 800
USA 6000
Re: trying to find solution! [message #347305 is a reply to message #347280] Thu, 11 September 2008 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@harshadsp

Read my post about formatting.
In addition you could read the other part of guidelines including this part:
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.


Thanks for the test case.

Regards
Michel
Re: trying to find solution! [message #347312 is a reply to message #347145] Thu, 11 September 2008 04:58 Go to previous message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

I apologize Michel.
Will keep this in mind.

regards
Harshad
Previous Topic: Hierarchical Query......
Next Topic: How to Find Last DML on a table
Goto Forum:
  


Current Time: Wed Dec 07 12:24:56 CST 2016

Total time taken to generate the page: 0.08979 seconds