Please help with this Query 1

[worldwise]

Hi,

I'm having trouble putting this query together. I have the following two tables.

VOLUNTEER
userName (FK)
hoursWorked

USERS
userName (PK)
firstName
lastName

I would like to run a query so I get:
firstName, lastName, totalHours

totalHours would be the sum off all volunteer hours logged for each person.

So I know that the Sum() function has to be used and that a GROUP BY on userName has to be done. My problem is getting the firstName, lastName into the mix.

Any help would be great!

Thanks.

 

[Golom]

Select V.UserName, U.FirstName, U.LastName,
       SUM(U.HoursWorked) As [Total Hours]

From  VOLUNTEER V INNER JOIN USERS U
      ON V.UserName = U.UserName

Group By V.UserName, U.FirstName, U.LastName

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[worldwise]

I get it, you just have to GROUP BY firstName and lastName just to make it work I guess?

Thanks~

 

[Golom]

Or to state it more generally ... you must GROUP BY those fields in the SELECT that are not arguments of an aggregate function such as SUM, MAX, MIN, AVG, COUNT, etc.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[worldwise]

Yeah, that makes sense.

Hey, I'm trying to throw in an "ORDER BY Total Hours" at the end but I get this error:

Error Type:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.

Thx again Golom.

 

[worldwise]

I got it to work using this: "ORDER BY SUM(V.hours) DESC"

Hopefully that is the correct way to do it.

 

[Golom]

Use

ORDER BY 4

There are some arcane rules about where you can or cannot use field aliases. This is one of them.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[worldwise]

Oh, I like that even better. I'll remember that one. Thanks!