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(departmen t_id)
GROUP BY department_id;


for any help


Thank you


Tamer

[Jon Armstrong]

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

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 "averageSal ary"
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" <tamer23@myreal box.com> wrote in message
news:c7r0sh$o2s $1@ngspool-d02.news.aol.co m...[color=blue]
> 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(departmen t_id)
> GROUP BY department_id;
>
>
> for any help
>
>
> Thank you
>
>
> Tamer[/color]




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