Select Statement that groups a group? I don't know how to ask.

[stinkyjak]

Select PID, ProjectName, UserName, Sum(Hours)
From WorkLog


WorkLog:

PID | projectName | UserName | HourS |
----------------------------------------
1 | Fist Project | Joe User | 2 |
1 | Fist Project | Joe User | 1 |
1 | Fist Project | BOB Cool | 2 |
2 | Second Proj | Joe User | 1 |
2 | Second Proj | Joe User | 3 |
1 | Fist Project | BOB Cool | 2 |


I am trying to produce this result:
PID | projectName | UserName | SUM of HourS |
-------------------------------------------------
1 | Fist Project | Joe User | 3 |
1 | Fist Project | BOB Cool | 5 |
2 | Second Proj | Joe User | 4 |


Thank you for your time. I am sure this is easy, but I am new. I promise I have looked for this answer before posting this new question.

 

[r937]

SELECT pid 
     , projectname 
     , username 
     , SUM(hours) AS sum_of_hours 
  FROM WorkLog 
GROUP
    BY pid 
     , projectname 
     , username

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[stinkyjak]

wow r937,
You are everywhere. Thank you again.

 

[stinkyjak]

Ok,

Can you help me take this farther? This query below partially works. The only two problems I have is that I can't return the ProjectName, which I know is easy.

But most importantly, the MgtHrs is showing the Resource hours for the project no matter who the Manager is. I need to show resource hours as '0' if the [User].UserName != Projects.Manager

SELECT ProjectID, MAX(MgtHrs) as MgtHrs, MAX(RHrs) as RHrs, SUM(WHrs) as WHrs, (MAX(MgtHrs) + MAX(RHrs)) AS TOTALHOUR, UserName
FROM
(SELECT Projects.ProjectName, Projects.ProjectID, isnull(Projects.ResourceHours, 0) as MgtHrs, isnull(Resources.FTE, 0) as RHrs, isnull(WorkLog.WorkTime, 0) as WHrs, [User].UserName
FROM Projects FULL OUTER JOIN
Resources ON Projects.ProjectID = Resources.ProjectID FULL OUTER JOIN
WorkLog ON Projects.ProjectID = WorkLog.ProjectID LEFT JOIN
[User] ON Projects.Manager LIKE [User].UserName OR WorkLog.UserName LIKE [User].UserName OR Resources.Resource LIKE [User].UserName
WHERE Projects.ProjectID = '38'
) AS MyTable
Group by ProjectID,UserName
Order by UserName


Producing:
ProjectID MgtHrs, RHrs, WHrs, TOTALHOUR, UserName
38 150 40 35.5000 190 Joe
38 150 150 71.0000 300 Bob

I want it to produce:
ProjectID MgtHrs, RHrs, WHrs, TOTALHOUR, UserName
38 0 40 35.5000 40 Joe
38 150 150 71.0000 300 Bob

 

[jaxtell]

I'm not sure this is ANSI compliant but I think CASE would work

SELECT  ProjectName, ProjectID, MAX(MgtHrs) as MgtHrs, MAX(RHrs) as RHrs, SUM(WHrs) as WHrs, (MAX(MgtHrs) + MAX(RHrs)) AS TOTALHOUR, UserName
FROM
(SELECT     Projects.ProjectName, Projects.ProjectID, case when Projects.Manager Like [User].UserName then isnull(Projects.ResourceHours, 0) else 0 end as MgtHrs, isnull(Resources.FTE, 0) as RHrs, isnull(WorkLog.WorkTime, 0) as WHrs, [User].UserName
FROM         Projects FULL OUTER JOIN
                      Resources ON Projects.ProjectID = Resources.ProjectID FULL OUTER JOIN
                      WorkLog ON Projects.ProjectID = WorkLog.ProjectID LEFT JOIN
                      [User] ON Projects.Manager LIKE [User].UserName OR WorkLog.UserName LIKE [User].UserName OR Resources.Resource LIKE [User].UserName
WHERE Projects.ProjectID = '38'
) AS MyTable
Group by ProjectID,UserName,ProjectName
Order by UserName

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[stinkyjak]

This is what I finally ended up using thanks to Naom in the asp.net forums.



SELECT MyTable2.ProjectID, Projects.ProjectName, MyTable2.MgtHrs, MyTable2.RHrs, MyTable2.WHrs, MyTable2.TOTALHOUR, MyTable2.UserNameFrom(SELECT ProjectID, MAX(MgtHrs) as MgtHrs, MAX(RHrs) as RHrs, SUM(WHrs) as WHrs, (MAX(MgtHrs) + MAX(RHrs)) AS TOTALHOUR, UserNameFROM(SELECT Projects.ProjectName, Projects.ProjectID,isnull(case when [User].UserName IS NOT NULL and Projects.Manager LIKE [User].UserName then Projects.ResourceHours else 0 end, 0) as MgtHrs,isnull(Resources.FTE, 0) as RHrs, isnull(WorkLog.WorkTime, 0) as WHrs, [User].UserNameFROM Projects FULL OUTER JOIN Resources ON Projects.ProjectID = Resources.ProjectID FULL OUTER JOIN WorkLog ON Projects.ProjectID = WorkLog.ProjectID LEFT JOIN [User] ON Projects.Manager LIKE [User].UserName OR WorkLog.UserName LIKE [User].UserName OR Resources.Resource LIKE [User].UserNameWhere ([User].UserName LIKE '%') AND (Projects.ProjectID = '38')) AS MyTableGroup by ProjectID,UserName) AS MyTable2Inner Join Projects On Projects.ProjectID = MyTable2.ProjectIDOrder by MyTable2.UserName
SELECT MyTable2.ProjectID, Projects.ProjectName, MyTable2.MgtHrs, MyTable2.RHrs, MyTable2.WHrs, MyTable2.TOTALHOUR, MyTable2.UserName

From

(SELECT ProjectID, MAX(MgtHrs) as MgtHrs, MAX(RHrs) as RHrs, SUM(WHrs) as WHrs, (MAX(MgtHrs) + MAX(RHrs)) AS TOTALHOUR, UserName

FROM

(SELECT Projects.ProjectName, Projects.ProjectID,



isnull(case when [User].UserName IS NOT NULL and Projects.Manager LIKE [User].UserName then Projects.ResourceHours else 0 end, 0) as MgtHrs,



isnull(Resources.FTE, 0) as RHrs, isnull(WorkLog.WorkTime, 0) as WHrs, [User].UserName

FROM Projects FULL OUTER JOIN

Resources ON Projects.ProjectID = Resources.ProjectID FULL OUTER JOIN

WorkLog ON Projects.ProjectID = WorkLog.ProjectID LEFT JOIN

[User] ON Projects.Manager LIKE [User].UserName OR WorkLog.UserName LIKE [User].UserName OR Resources.Resource LIKE [User].UserName



Where ([User].UserName LIKE '%') AND (Projects.ProjectID = '38')



) AS MyTable

Group by ProjectID,UserName

) AS MyTable2

Inner Join Projects On Projects.ProjectID = MyTable2.ProjectID





Order by MyTable2.UserName

 

[MarcLodge]

No disrespect stinkyjak, and it may be the fact that you've not used the TGML CODE statement, but your code is almost unreadable. How did Rudy's:
SELECT pid
, projectname
, username
, SUM(hours) AS sum_of_hours
FROM WorkLog
GROUP BY pid
, projectname
, username

become the monstrosity in your last post?

If this is for a commercial application, the manager of the support department that has to take this on might well ask you to re-write it!