| sql [message #627554] |
Wed, 12 November 2014 13:21  |
 |
sanodani
Messages: 98
Registered: October 2014
|
Member |
|
|
Hallo 
I am trying to make a query, the all departments, that has minimum one workers, that is more than 20 year working in the same department.
and I wanna display: name of dept, city, country, region, the number of workers that, they are more than 20 years working in the dept, and total number of workers.
but my query did not show me result ?:/ can anyone help me please ?
thnakx in advance
Select d.department_id, e.employee_id, d.department_name
, c.country_name, r.region_name, count(*)
, sum(salary)
from employees e, locations l, countries c, regions r, departments d
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.country_id = c.country_id
and c.region_id = r.region_id
and trunc(sysdate) -hire_date > 20*365
group by d.department_id, e.employee_id, d.department_name, l.city, c.country_name, r.region_name
order by employee_id; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: sql [message #627562 is a reply to message #627560] |
Wed, 12 November 2014 13:52  |
|
sanodani
Messages: 98
Registered: October 2014
|
Member |
|
|
13/01/2001
well, may be i got now what you mean thankyou very much
there is no one working since 20 years.
but, do you think, i am correct to get my result as asked in the question in first post?
Select d.department_id, e.employee_id, d.department_name
, c.country_name, r.region_name, count(*)
, sum(salary)
from employees e, locations l, countries c, regions r, departments d
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.country_id = c.country_id
and c.region_id = r.region_id
and trunc(sysdate) -hire_date > 10*365
group by d.department_id, e.employee_id, d.department_name, l.city, c.country_name, r.region_name
order by department_id; |
|
|
|
|
|
| Re: sql [message #627565 is a reply to message #627564] |
Wed, 12 November 2014 14:05 |
|
sanodani
Messages: 98
Registered: October 2014
|
Member |
|
|
sorry i have seen your reply late..
so here is my query again, but still i have error 
Select d.department_id, e.employee_id, d.department_name
, c.country_name, r.region_name, count(*)
, sum(salary)
from employees e, locations l, countries c, regions r, departments d
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.country_id = c.country_id
and c.region_id = r.region_id
and add_months(trunc(sysdate,-20)*12)
group by d.department_id, e.employee_id, d.department_name, l.city, c.country_name, r.region_name
order by department_id; |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
