CODEWITHSUNDEEP
sqlsql-servergroup-byunion
Luis Lara
Luis Lara

Reputation: 75

GROUP BY in one single record

I'm working on SQL Server and I need help with a query.

This is the scenario.

I have two tables Forecast and Orders.

There are some fields that match on both tables and other fields don't match.

It doesn't matter I can have all of them on a SELECT.

However, there are just 2 fields that should always match. ID and SKU.

At first I tried to use a LEFT JOIN but this doesn't work because I need one single column for ID and SKU.

Then I tried to UNION both tables and group by them.

Here are the code for CREATE and INSERT.

CREATE TABLE [dbo].[Tbl_Forecast_Test](
[ID] [varchar](100) NULL,
[CUST_ID] [varchar](40) NULL,
[MFG_PART_NUMBER] [varchar](40) NULL,
[SEGMENT] [varchar](40) NULL,
[WH_NUMBER] [varchar](40) NULL,
[RTM] [varchar](40) NULL,
[FORECAST] [int] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[Tbl_Orders_Test](
[ID] [varchar](100) NULL,
[END_CUST_ID] [varchar](40) NULL,
[PROD_ID] [varchar](40) NULL,
[PIPELINE] [varchar](40) NULL,
[WAREHOUSE] [varchar](40) NULL,
[RTM_GROUP] [varchar](40) NULL,
[ORDERS] [int] NULL
) ON [PRIMARY]

INSERT INTO [BOSSTest].[dbo].[Tbl_Forecast_Test]
([ID],[CUST_ID],[MFG_PART_NUMBER],[SEGMENT],[WH_NUMBER],[RTM],[FORECAST])
VALUES
('US8098629','','W0P88EP','Comm','471','Direct','10')

INSERT INTO [BOSSTest].[dbo].[Tbl_Orders_Test]
([ID],[END_CUST_ID],[PROD_ID],[PIPELINE],[WAREHOUSE],[RTM_GROUP],[ORDERS])
SELECT 'US8098629','W17445','W0P88EP','Ent Group','1','Direct','5'
UNION ALL
SELECT 'US8098629','W17445','V9G85EP','Ent Group','460','Direct','12'
UNION ALL
SELECT 'US8098629','W17445','V9G85EP','Ent Group','460','Direct','29'

This is the close that I get.

 SELECT  

 [ID]
,[CUST_ID]
,[END_CUST_ID]
,[SKU]
,[SEGMENT]
,[PIPELINE]
,[WH_NUMBER]
,[WAREHOUSE]
,[RTM] 
,[RTM_GROUP]
,SUM ([FORECAST]) AS [FORECAST]
,SUM([ORDERS]) AS [ORDER_LOAD]

FROM ( 

     SELECT  

     [ID] AS [ID]
    ,[CUST_ID]
    ,'' AS [END_CUST_ID]
    ,[MFG_PART_NUMBER] AS [SKU]
    ,[SEGMENT]
    ,'' AS [PIPELINE]
    ,[WH_NUMBER]
    ,'' AS [WAREHOUSE]
    ,[RTM] 
    ,'' AS [RTM_GROUP]
    ,SUM ([FORECAST]) AS [FORECAST]
    ,0 AS [ORDERS]

    FROM [BOSSTest].[dbo].[Tbl_Forecast_Test] 

    GROUP BY
     [ID]
    ,[CUST_ID]
    ,[MFG_PART_NUMBER]
    ,[SEGMENT]
    ,[WH_NUMBER]
    ,[RTM] 

    UNION ALL

    SELECT 

    [ID] AS [ID]
    ,'' AS [CUST_ID]
    ,[END_CUST_ID]
    ,[PROD_ID] AS [SKU]
    ,'' AS [SEGMENT]
    ,[PIPELINE]
    ,'' AS [WH_NUMBER]
    ,[WAREHOUSE]
    ,'' AS [RTM]
    ,[RTM_GROUP]
    ,0 AS [FORECAST]
    ,SUM([ORDERS]) AS [ORDERS]

    FROM [BOSSTest].[dbo].[Tbl_Orders_Test] 

    GROUP BY
     [ID]
    ,[END_CUST_ID]
    ,[PROD_ID] 
    ,[PIPELINE]
    ,[WAREHOUSE]
    ,[RTM_GROUP]


) AS TEST

GROUP BY
 [ID]
,[CUST_ID]
,[END_CUST_ID]
,[SKU]
,[SEGMENT]
,[PIPELINE]
,[WH_NUMBER]
,[WAREHOUSE]
,[RTM] 
,[RTM_GROUP]

This is the result when I execute the query.

+---------+-------+-----------+-------+-------+---------+---------+---------+------+---------+--------+----------+
|    ID   |CUST_ID|END_CUST_ID|SKU    |SEGMENT|PIPELINE |WH_NUMBER|WAREHOUSE| RTM  |RTM_GROUP|FORECAST|ORDER_LOAD|
+---------+-------+-----------+-------+-------+---------+---------+---------+------+---------+--------+----------+
|US8098629|       |           |W0P88EP| Comm  |         |   471   |         |Direct|         |      10|         0|
|US8098629|       | W17445    |V9G85EP|       |Ent Group|         |      460|      |   Direct|       0|        41|
|US8098629|       | W17445    |W0P88EP|       |Ent Group|         |        1|      |   Direct|       0|         5|
+---------+-------+-----------+-------+-------+---------+---------+---------+------+---------+--------+----------+

But this is what I need. I want to keep the different SKUs in the table.

+---------+-------+-----------+-------+-------+---------+---------+---------+------+---------+--------+----------+
|    ID   |CUST_ID|END_CUST_ID|SKU    |SEGMENT|PIPELINE |WH_NUMBER|WAREHOUSE| RTM  |RTM_GROUP|FORECAST|ORDER_LOAD|
+---------+-------+-----------+-------+-------+---------+---------+---------+------+---------+--------+----------+
|US8098629|       | W17445    |W0P88EP| Comm  |Ent Group|   471   |        1|Direct|   Direct|      10|         5|
|US8098629|       | W17445    |V9G85EP|       |Ent Group|         |      460|      |   Direct|       0|        41|
+---------+-------+-----------+-------+-------+---------+---------+---------+------+---------+--------+----------+

Any suggestion on how can I group ID = US8098629 and SKU = W0P88EP in one single record.

Thanks in advance, Luis

Upvotes: 1

Views: 88

Answers (4)

Mansoor
Mansoor

Reputation: 4192

SELECT #Tbl_Orders_Test.[ID],ISNULL([CUST_ID],'') [CUST_ID],[END_CUST_ID],PROD_ID] [SKU],ISNULL([SEGMENT],'') [SEGMENT],[PIPELINE],ISNULL([WH_NUMBER],'') [WH_NUMBER],[WAREHOUSE],ISNULL([RTM],'')[RTM],[RTM_GROUP],ISNULL(SUM ([FORECAST]),0) AS [FORECAST],SUM([ORDERS]) AS [ORDER_LOAD] FROM #Tbl_Orders_Test LEFT OUTER JOIN #Tbl_Forecast_Test ON #Tbl_Forecast_Test.[ID] = #Tbl_Orders_Test.[ID] AND #Tbl_Forecast_Test [MFG_PART_NUMBER] = #Tbl_Orders_Test.[PROD_ID] GROUP BY #Tbl_Orders_Test.[ID],[CUST_ID],[END_CUST_ID],[PROD_ID] ,[SEGMENT],[PIPELINE],[WH_NUMBER],[WAREHOUSE],[RTM] ,[RTM_GROUP] ORDER BY [FORECAST] DESC

Upvotes: 0

Dave
Dave

Reputation: 21

At first glance it looks like you could stop grouping by "END_CUST_ID", and instead pull the first. 

SELECT  
     [ID]
    ,[CUST_ID]
    ,Max([END_CUST_ID]) as [END_CUSTID]
    ,[SKU]
    ,[SEGMENT]
    ,[PIPELINE]
    ,[WH_NUMBER]
    ,[WAREHOUSE]
    ,[RTM] 
    ,[RTM_GROUP]
    ,SUM ([FORECAST]) AS [FORECAST]
    ,SUM([ORDERS]) AS [ORDER_LOAD]

    FROM ( 

         SELECT  

         [ID] AS [ID]
        ,[CUST_ID]
        ,'' AS [END_CUST_ID]
        ,[MFG_PART_NUMBER] AS [SKU]
        ,[SEGMENT]
        ,'' AS [PIPELINE]
        ,[WH_NUMBER]
        ,'' AS [WAREHOUSE]
        ,[RTM] 
        ,'' AS [RTM_GROUP]
        ,SUM ([FORECAST]) AS [FORECAST]
        ,0 AS [ORDERS]

        FROM [BOSSTest].[dbo].[Tbl_Forecast_Test] 

        GROUP BY
         [ID]
        ,[CUST_ID]
        ,[MFG_PART_NUMBER]
        ,[SEGMENT]
        ,[WH_NUMBER]
        ,[RTM] 

        UNION ALL

        SELECT 

        [ID] AS [ID]
        ,'' AS [CUST_ID]
        ,[END_CUST_ID]
        ,[PROD_ID] AS [SKU]
        ,'' AS [SEGMENT]
        ,[PIPELINE]
        ,'' AS [WH_NUMBER]
        ,[WAREHOUSE]
        ,'' AS [RTM]
        ,[RTM_GROUP]
        ,0 AS [FORECAST]
        ,SUM([ORDERS]) AS [ORDERS]

        FROM [BOSSTest].[dbo].[Tbl_Orders_Test] 

        GROUP BY
         [ID]
        ,[END_CUST_ID]
        ,[PROD_ID] 
        ,[PIPELINE]
        ,[WAREHOUSE]
        ,[RTM_GROUP]

    ) AS TEST

    GROUP BY
     [ID]
    ,[CUST_ID]
    ,[SKU]
    ,[SEGMENT]
    ,[PIPELINE]
    ,[WH_NUMBER]
    ,[WAREHOUSE]
    ,[RTM] 
    ,[RTM_GROUP]

Upvotes: -1

Derrick Moeller
Derrick Moeller

Reputation: 4950

You stated you couldn't use a left join because you needed a single column for both ID and SKU.

With this in mind, you should still be able to use a left join. You don't have to select every column from both tables after performing the join. Include or omit columns as needed for your requirements.

SELECT ot.ID, ft.CUST_ID, ot.END_CUST_ID, ot.PROD_ID AS SKU, ft.SEGMENT,
       ot.PIPELINE, ft.WH_NUMBER, ot.WAREHOUSE, ft.RTM, ot.RTM_GROUP,
       SUM(ft.FORECAST) AS FORECAST, SUM(ot.ORDERS) AS ORDERS
FROM Tbl_Orders_Test ot
LEFT JOIN Tbl_Forecast_Test ft ON ft.ID = ot.ID AND ft.MFG_PART_NUMBER = ot.PROD_ID
GROUP BY ot.ID, ft.CUST_ID, ot.END_CUST_ID, ot.PROD_ID, ft.SEGMENT,
         ot.PIPELINE, ft.WH_NUMBER, ot.WAREHOUSE, ft.RTM, ot.RTM_GROUP

Upvotes: 2

Sandeep Garg
Sandeep Garg

Reputation: 19

You can use below solution ,

       SELECT   [ID],[CUST_ID],max([END_CUST_ID]),[SKU],max([SEGMENT]),max([PIPELINE]),max([WH_NUMBER]),max([WAREHOUSE]),max([RTM]) ,max([RTM_GROUP]),SUM ([FORECAST]) AS [FORECAST],SUM([ORDERS]) AS [ORDER_LOAD]

 FROM ( 
 SELECT  

 [ID] AS [ID]    ,[CUST_ID]    ,'' AS [END_CUST_ID]    ,[MFG_PART_NUMBER] AS [SKU]    ,[SEGMENT]    ,'' AS [PIPELINE]    ,[WH_NUMBER]    ,'' AS [WAREHOUSE]
,[RTM]     ,'' AS [RTM_GROUP]    ,SUM ([FORECAST]) AS [FORECAST]    ,0 AS [ORDERS]
FROM [##Tbl_Forecast_Test] 

GROUP BY      [ID]   ,[CUST_ID]    ,[MFG_PART_NUMBER]    ,[SEGMENT]    ,[WH_NUMBER]    ,[RTM] 
UNION ALL

SELECT      [ID] AS [ID]    ,'' AS [CUST_ID]    ,[END_CUST_ID]    ,[PROD_ID] AS [SKU]    ,'' AS [SEGMENT]    ,[PIPELINE]    ,'' AS [WH_NUMBER]    ,[WAREHOUSE]
,'' AS [RTM]    ,[RTM_GROUP]    ,0 AS [FORECAST]    ,SUM([ORDERS]) AS [ORDERS]
    FROM [##Tbl_Orders_Test] 

   GROUP BY
 [ID]    ,[END_CUST_ID]    ,[PROD_ID]     ,[PIPELINE]    ,[WAREHOUSE]    ,[RTM_GROUP]) AS TEST
  GROUP BY
  [ID],[CUST_ID],[SKU]

Upvotes: 0

Related Questions