Query Help 2

[DANZIG]

Hello,
I was trying to find the best query to use on the table below. All of the results are in one table and the wold all have the same project number so where projectnum ='1'

UserNo RptMo RptHr RptYr
User1	06	1	2004
User3	05	6	2004
User2	08	3	2004
User1	06	3	2004
User1	03	12   2003
User1	09	8	2004
User4	05	8	2004

I'd like to return the total hours per user sorted by user then by month and year. so the return would be something like.

UserNo RptHr RptMo RptYr
User1 12 03 2003
User1 4 06 2004
User1 8 09 2004
User2 3 08 2004
User3 6 05 2004
User4 8 05 2004


I've tried this

SELECT UserNo, SUM(RptHr)
FROM db_projects where ProjectID = '1' group by UserNo

But it doesn't even seem close 8/



Thanks, Danzig

 

[amorous]

does this work:

SELECT UserNo, SUM(RptHr),RptMo,RptYr
FROM db_projects where ProjectID = '1' group by UserNo,RptMo,RptYr Order by RptMo Asc, RptYr Asc

-VJ

 

[PushCode]

Maybe something like this will work.

Untested:

SELECT UserNo, SUM(RptHr) AS ttl_hrs, RptMo, RptYr
FROM db_projects where ProjectID = '1'
ORDER BY UserNo, RptMo, RptYr

 

[DANZIG]

Thank you both very much, the solution was a combination of both queries.


Thanks, Danzig

 

[DANZIG]

What if you wanted to take this a step further and pull 1 field of data from another table in the return?

IE lst_projects.project where db_projects.projectID = lst_projects.Idx

There are two table I need the return from the previous query to get the data but now the project is represented by a number instead of the project name which is linked to the number in the lst_projects table via an IDX field.


Thanks, Danzig

 

[amorous]

Something like this:

SELECT UserNo, SUM(RptHr),RptMo,RptYr,lst_projects.project
FROM db_projects INNER JOIN lst_projects ON
db_projects.projectID = lst_projects.Idx
where ProjectID = '1' group by UserNo,RptMo,RptYr Order by RptMo Asc, RptYr Asc

-VJ