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? There's nothing
denormalized about the schema in question:
USER:
userID
Firstname
Lastname
Username
Password
...
TIMELOG:
timelogID
userID
startTime
endTime
logEntry
> -----Original Message-----
> From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 08, 2004 3:06 PM
> To: CF-Talk
> Subject: Re: Query of a query inconsistancies
>
> Barney Boisvert wrote:
> >> For some reason in MySQL this braindead syntax is allowed, but
> >> even the manual says the result may be 'unpredictable'
> >
> > It's not totally brain dead.
>
> I disagree :)
>
>
> > I agree that if you don't know what you're
> > doing, you shouldn't use it, but it is nice where you've
> got a series of
> > columns that you're grouping on, but one of them is unique.
>
> If only one of them is unique, you need to normalize your data :)
>
> But even in the case you are working with such a schema, go for
> listing all the columns in the group by, especially if there is
> no way of enforcing that all future data will also have those
> characteristics. It is just to prone to future errors, not just
> the usual errors introduced by changing code, but also errors
> introduced by changing data.
>
> Jochem
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
