Luis Lara Luis Lara - 1 month ago 5
SQL Question

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

Answer

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
Comments