technette avatar image
technette asked

Grouping Data

Hi, I am trying to apply grouping as in this sample query to my query and don't understand how to group by Resource Code. I'm required to group by Resource Code( CI.description and CI.Mapping) should also be include in the group. Each group must show total labor hours by workorder. USE AdventureWorks2008R2; GO SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country' ,S.Name AS N'Store', H.SalesPersonID ,SUM(TotalDue) AS N'Total Sales' FROM Sales.Customer C INNER JOIN Sales.Store AS S ON C.StoreID = S.BusinessEntityID INNER JOIN Sales.SalesTerritory AS T ON C.TerritoryID = T.TerritoryID INNER JOIN Sales.SalesOrderHeader AS H ON C.CustomerID = H.CustomerID WHERE T.[Group] = N'Europe' AND T.CountryRegionCode IN(N'DE', N'FR') AND H.SalesPersonID IN(287, 288, 290) AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ') GROUP BY GROUPING SETS( (T.[Group], T.CountryRegionCode) ,(S.Name) ,(H.SalesPersonID,T.[Group]) ,(H.SalesPersonID) ,()) ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID; **Here's My query** SELECT WO.Base_ID + '/' + WO.Lot_ID + '.' + WO.Split_ID AS WorkOrder, WO.Part_ID AS PartID, P.Description AS PartDesc, WO.Status AS Status, WO.Create_date, WO.Desired_Rls_Date As ReleaseDate, P.planner_user_id AS Planne, WO.Printed_Date As PrintDate, WO.Desired_Qty AS OrderQty, LT.OPERATION_SEQ_NO, LT.RESOURCE_ID, CI.Description, CI.Mapping, SUM(ISNULL(LT.HOURS_WORKED, 0)) AS LABOR_HOURS, LT.DEPARTMENT_ID, E.First_Name + ' ' + E.Last_Name AS EMPLOYEE FROM Work_Order WO LEFT JOIN Part P ON P.ID = WO.Part_ID LEFT JOIN LABOR_TICKET LT ON WO.BASE_ID = LT.WORKORDER_BASE_ID LEFT JOIN EMPLOYEE E ON LT.EMPLOYEE_ID = E.ID LEFT JOIN Ontic.dbo.CI_Resource_Codes CI ON CI.ResourceID = LT.RESOURCE_ID WHERE (WO.Desired_Rls_Date BETWEEN '12/10/2013' AND '12/10/2015') GROUP BY WO.Base_ID + '/' + WO.Lot_ID + '.' + WO.Split_ID, WO.PART_ID,P.DESCRIPTION,WO.STATUS,wo.CREATE_DATE,wo.DESIRED_RLS_DATE, p.PLANNER_USER_ID,wo.PRINTED_DATE,wo.DESIRED_QTY, LT.OPERATION_SEQ_NO, lt.RESOURCE_ID, CI.description, CI.Mapping, LT.DEPARTMENT_ID, E.First_Name + ' ' + E.Last_Name
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Tom Staab avatar image
Tom Staab answered
GROUPING SETS is a powerful option that allows you to group and get subtotals at multiple levels. The empty set is used to get a grand total. Here is the syntax: GROUP BY GROUPING SETS ( (col1, col2, col3) -- list of columns for first grouping , (col1, col2) -- list of columns for second grouping , (col4) -- different column for other grouping , () -- grand total ) In your case, it sounds like you need every column in your current GROUP BY listed in one grouping and then those 2 columns (CI.description and CI.mapping) listed in another.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.