×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

thread220-1576732

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

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

(OP)
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.  

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

CODE

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

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

(OP)
wow r937,
You are everywhere. Thank you again.

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

(OP)
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



 

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

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

CODE


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/

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

(OP)
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

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

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!  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close