Re: simple? join - Mailing list pgsql-sql

From Peter Eisentraut
Subject Re: simple? join
Date
Msg-id Pine.LNX.4.30.0201060020420.2868-100000@peter.localdomain
Whole thread Raw
In response to simple? join  (Frank Bax <fbax@sympatico.ca>)
Responses Re: simple? join
List pgsql-sql
Frank Bax writes:

> EMPLOYEE table contains: emp, first, last (emp is unique key)
> TIMESHEET table contains: emp, timestamp, hours
>
> I want to report employee name and total hours.
>
> So far, I've come up with:
>
> SELECT min(emp.first) as first, min(emp.last) as last, sum(ts.hours)
>   FROM timesheet ts, employee emp
>  WHERE ts.emp = emp.emp
>  GROUP by emp.emp
>  ORDER BY last, first;
>
> It seems silly to specify "min" when I know there is only one value per emp!

Indeed this looks strange.

> SELECT first, last, sum(ts.hours)
>   FROM timesheet ts, employee emp
>  WHERE ts.emp = emp.emp
>  GROUP by emp.emp, first, last
>  ORDER BY last, first;
>
> It seems silly to specify extraneous "group by" fields.

There's nothing "extraneous" there.  Both first and last could be
duplicated, so you need to group by each one.

> Is there a 'better' way to write this SQL?

Not really.  The latter is how I would write it.

-- 
Peter Eisentraut   peter_e@gmx.net



pgsql-sql by date:

Previous
From: Andrew Perrin
Date:
Subject: Which date/paper pairs are NOT represented?
Next
From: "Hunter, Ray"
Date:
Subject: Postgres vs. Redhat DB