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

[Tamer Higazi]

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

[Jon Armstrong]

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! =-----