Link to home
Start Free TrialLog in
Avatar of nadun
nadun

asked on

Group by result set

Hi

i have a query which print Repeat lines  but i want to group them by  Employee and just want to print one line for one employee

the query...
 
SELECT     sOrder.OrderNo AS [Order No.], sOrder.Description AS [Order Description], uRALBase.RALBase AS [RAL W/O Base],
                      lAgency.AgencyName AS [Agency Name], RTRIM(lEmployee.FirstName) + ' ' + RTRIM(lEmployee.Surname) AS Employee,
                      lEmployeeRange.Description AS [Employee Range],
cast(sum(lEmployeeDayHours.DurationMinutes)/60.00 AS float) AS [Duration Minutes],
cast(sum(lEmployeeDayHours.HoursBilledMinutes)/60.00 AS float) AS [Hours Billed Minutes],

Case when (lTimeCategory.TimeCategory = 'TRV') then cast(sum(lEmployeeDayHours.DurationMinutes)/60.00 AS float) else '' end AS [Duration Minutes TRV],
Case when (lTimeCategory.TimeCategory = 'TRV') then cast(sum(lEmployeeDayHours.HoursBilledMinutes)/60.00 AS float) else '' end AS [HoursBilledMinutes TRV]
     
FROM         lEmployeeDayHours INNER JOIN
                      lEmployeeDay ON lEmployeeDayHours.lEmployeeDay_ID = lEmployeeDay.ID INNER JOIN
                      sOrderTask ON lEmployeeDayHours.sOrderTask_ID = sOrderTask.ID INNER JOIN
                      sOrder ON sOrderTask.sOrder_ID = sOrder.ID INNER JOIN
                      lEmployee ON lEmployeeDayHours.lEmployee_ID = lEmployee.ID INNER JOIN
                      uRALBase ON sOrder.uRALBase_ID = uRALBase.ID INNER JOIN
                      lAgency ON lEmployee.lAgency_ID = lAgency.ID INNER JOIN
                      lEmployeeRange ON lEmployee.lEmployeeRange_ID = lEmployeeRange.ID LEFT OUTER JOIN
                      lTimeCategory ON lEmployeeDayHours.lTimeCategory_ID = lTimeCategory.ID LEFT OUTER JOIN
                      lHoursCode ON lEmployeeDayHours.lHoursCode_ID = lHoursCode.ID LEFT OUTER JOIN
                      sOrderTaskType ON sOrderTask.sOrderTaskType_ID = sOrderTaskType.ID
WHERE     (lEmployeeDay.EnterWorkTime >= '2006-01-01T00:00:00') AND (lEmployeeDay.EnterWorkTime <= '2006-12-31T00:00:00') AND
                      (lTimeCategory.TimeCategory <> 'TRV') AND (SUBSTRING(sOrder.OrderNo, 1, 3) <> 'RAL')
                       AND uRALBase.RALBase <> 'Coventry' AND  lAgency.AgencyName = 'RAL UK Staff'


GROUP BY lEmployeeRange.Description, RTRIM(lEmployee.FirstName) + ' ' + RTRIM(lEmployee.Surname), sOrder.OrderNo, sOrder.Description, uRALBase.RALBase, lAgency.AgencyName
,lTimeCategory.TimeCategory

The result set..
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Chris Williams      UK Installations and Support      40.5      42.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Chris Williams      UK Installations and Support      34.5      42.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Chris Williams      UK Installations and Support      39.5      35.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Chris Williams      UK Installations and Support      2.0      0.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Chris Williams      UK Installations and Support      2.0      0.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Chris Williams      UK Installations and Support      12.0      7.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Jason Bartlett      UK Installations and Support      103.5      93.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Jason Bartlett      UK Installations and Support      1.5      0.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Jason Bartlett      UK Installations and Support      1.0      0.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Jason Bartlett      UK Installations and Support      36.0      28.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Jason Bartlett      UK Installations and Support      59.5      56.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Paul Saunders      UK Installations and Support      26.5      28.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Paul Saunders      UK Installations and Support      8.5      7.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Royston Neal      UK Installations and Support      23.5      28.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Royston Neal      UK Installations and Support      2.0      7.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Royston Neal      UK Installations and Support      7.5      7.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           RAL UK Staff                                  Royston Neal      UK Installations and Support      13.0      21.0      0.0      0.0

But I just want to print like....

BRF-P1      Bahrain Royal Flight Installation      Dubai           Chris Williams      UK Installations and Support      130.5      126.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           Jason Bartlett      UK Installations and Support      201.5      177.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           Paul Saunders      UK Installations and Support      35.0      35.0      0.0      0.0
BRF-P1      Bahrain Royal Flight Installation      Dubai           Royston Neal      UK Installations and Support      46.0      63.0      0.0      0.0

That summing

sum([Duration Minutes]) as [Duration Minutes],
       Sum([Hours Billed Minutes])as [Hours Billed Minutes],
       Sum([Duration Minutes TRV]) as [Duration Minutes TRV] ,
       Sum([HoursBilledMinutes TRV]) as [HoursBilledMinutes TRV]

Please Help

Thanks
Na


 
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nadun
nadun

ASKER

I have got this error

Server: Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'GROUP'.
Avatar of nadun
nadun

ASKER

select [Order No.], [Order Description], [RAL W/O Base], [Agency Name], [Employee], sum([Duration Minutes]) AS [Duration Minutes], sum([Hours Billed Minutes]) AS [Hours Billed Minutes], sum([Duration Minutes TRV]) AS [Duration Minutes TRV], sum([Hours Billed Minutes TRV]) AS [Hours Billed Minutes TRV] FROM
(SELECT     sOrder.OrderNo AS [Order No.], sOrder.Description AS [Order Description], uRALBase.RALBase AS [RAL W/O Base],
                      lAgency.AgencyName AS [Agency Name], RTRIM(lEmployee.FirstName) + ' ' + RTRIM(lEmployee.Surname) AS Employee,
                      lEmployeeRange.Description AS [Employee Range],
cast(sum(lEmployeeDayHours.DurationMinutes)/60.00 AS float) AS [Duration Minutes],
cast(sum(lEmployeeDayHours.HoursBilledMinutes)/60.00 AS float) AS [Hours Billed Minutes],

Case when (lTimeCategory.TimeCategory = 'TRV') then cast(sum(lEmployeeDayHours.DurationMinutes)/60.00 AS float) else '' end AS [Duration Minutes TRV],
Case when (lTimeCategory.TimeCategory = 'TRV') then cast(sum(lEmployeeDayHours.HoursBilledMinutes)/60.00 AS float) else '' end AS [HoursBilledMinutes TRV]
     
FROM         lEmployeeDayHours INNER JOIN
                      lEmployeeDay ON lEmployeeDayHours.lEmployeeDay_ID = lEmployeeDay.ID INNER JOIN
                      sOrderTask ON lEmployeeDayHours.sOrderTask_ID = sOrderTask.ID INNER JOIN
                      sOrder ON sOrderTask.sOrder_ID = sOrder.ID INNER JOIN
                      lEmployee ON lEmployeeDayHours.lEmployee_ID = lEmployee.ID INNER JOIN
                      uRALBase ON sOrder.uRALBase_ID = uRALBase.ID INNER JOIN
                      lAgency ON lEmployee.lAgency_ID = lAgency.ID INNER JOIN
                      lEmployeeRange ON lEmployee.lEmployeeRange_ID = lEmployeeRange.ID LEFT OUTER JOIN
                      lTimeCategory ON lEmployeeDayHours.lTimeCategory_ID = lTimeCategory.ID LEFT OUTER JOIN
                      lHoursCode ON lEmployeeDayHours.lHoursCode_ID = lHoursCode.ID LEFT OUTER JOIN
                      sOrderTaskType ON sOrderTask.sOrderTaskType_ID = sOrderTaskType.ID
WHERE     (lEmployeeDay.EnterWorkTime >= '2006-01-01T00:00:00') AND (lEmployeeDay.EnterWorkTime <= '2006-12-31T00:00:00') AND
                      (lTimeCategory.TimeCategory <> 'TRV') AND (SUBSTRING(sOrder.OrderNo, 1, 3) <> 'RAL')
                       AND uRALBase.RALBase <> 'Coventry' AND  lAgency.AgencyName = 'RAL UK Staff'


GROUP BY lEmployeeRange.Description, RTRIM(lEmployee.FirstName) + ' ' + RTRIM(lEmployee.Surname), sOrder.OrderNo, sOrder.Description, uRALBase.RALBase, lAgency.AgencyName
,lTimeCategory.TimeCategory) as XX
GROUP BY [Order No.], [Order Description], [RAL W/O Base], [Agency Name], [Employee];



This is the right query
Avatar of awking00
awking00
Flag of United States of America image
Other than adding the alias, "as XX," what was the difference?