>> If only one of them is unique, you need to normalize your data :)
>
> I need to pull out a list of users and the number of hours they've worked in
> the past year:
>
> SELECT user.userID, user.firstname, user.lastname,
> SUM(timelog.endTime - timelog.startTime) AS timeWorked
> FROM user
> INNER JOIN timelog ON user.userID = timelog.timelogID
> GROUP BY user.userID [ , user.firstname, user.lastname ]
> ORDER BY user.lastname, user.firstname, user.userID
>
> Why bother having the full select list (minus the aggregate column) in the
> GROUP BY clause, when simply 'user.userID' will do the job?
As long as your data is normalized, standard SQL doesn't require
you to have anything more in the GROUP BY clause as MySQL does as
long as you are smart about your SQL:
SELECT u.userID, u.firstname, u.lastname, t.timeWorked
FROM user u INNER JOIN (
SELECT timelogID, SUM(endTime - startTime) AS timeWorked
FROM timelog
GROUP BY timelogID
) t ON u.userID = t.timelogID
ORDER BY user.lastname, user.firstname, user.userID
Naturally this requires you to have a DBMS that supports
subqueries and this is a query one would typically implement
using a view if it occured frequently. I believe the (then) lack
of support for subqueries and the current lack of support for
views in MySQL is a better explanation for the presence of this
'feature' as this being an omission in the SQL standard.
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
