469,344 Members | 5,990 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

creating joins between tables with "group by" clause to limit rows

Hi!
I have problems creating joins between tables and to limit rows for a
specified clolumn. I have 2 tables!

employees
departments

I face the problems with the GROUP BY clause and i don't get it handled
solving that problem. Neither with oracle sql or SQL1999 syntax!

Oracle SQL:

SELECT
dep.department_name as "Name",
dep.location_id as "Location",
emp.salary AS "Salary"
FROM employees emp, departments dep
WHERE (emp.department_id = dep.department_id)
GROUP BY dep.department_name;

SQL1999:

SELECT
department_name as "Name",
location_id as "Location",
salary AS "Salary"
FROM employees emp
JOIN departments dep
USING(department_id)
GROUP BY department_id;
for any help
Thank you
Tamer
Jul 19 '05 #1
1 16792
Tamer,

Since you didn't actually ask a question, I'll just offer this:

Find a nice tutorial or introductory SQL text.

With GROUP BY, your immediate select list is limited to aggregates of the
group.

In your example, it's fine to have department_name in the select list, but
not proper to have location_id or salary in the select list, since they are
not
aggregates of the group. One solution would be to add those items to the
GROUP BY list, but often that's not appropriate.

In your case, it appears that location_id could appropriately be added to
the select list, but I doubt salary would be.

For salary, you would more likely have to choose some reasonable aggregate
to show, like:

SELECT
dep.department_name as "Name",
dep.location_id as "Location",
avg(emp.salary) AS "averageSalary"
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id
GROUP BY dep.department_name, dep.location_id;
If you really want employee specific results, I suspect you don't want to
use
GROUP BY at all. You might just want to ORDER BY department_name
instead.

SELECT
dep.department_name as "Name",
dep.location_id as "Location",
emp.salary AS "Salary"
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id
ORDER BY dep.department_name;
Best regards... Jon

Jon Armstrong
"Tamer Higazi" <ta*****@myrealbox.com> wrote in message
news:c7**********@ngspool-d02.news.aol.com...
Hi!
I have problems creating joins between tables and to limit rows for a
specified clolumn. I have 2 tables!

employees
departments

I face the problems with the GROUP BY clause and i don't get it handled
solving that problem. Neither with oracle sql or SQL1999 syntax!

Oracle SQL:

SELECT
dep.department_name as "Name",
dep.location_id as "Location",
emp.salary AS "Salary"
FROM employees emp, departments dep
WHERE (emp.department_id = dep.department_id)
GROUP BY dep.department_name;

SQL1999:

SELECT
department_name as "Name",
location_id as "Location",
salary AS "Salary"
FROM employees emp
JOIN departments dep
USING(department_id)
GROUP BY department_id;
for any help
Thank you
Tamer



-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by mark | last post: by
reply views Thread by TaeHo Yoo | last post: by
2 posts views Thread by littlebeam | last post: by
1 post views Thread by Thomas Qi | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.