SQL Subquery , One to Multiple rows Following query I have written returns the result as expected, example 121350 rows.   

SELECT dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id,
       SUM(
           ISNULL(dbo.COMPONENT.Component_weight, 0) * ISNULL(dbo.ORDER_ALLOCATE.Order_allocate_qty, 0)
       ) AS Component_weight,
       COUNT_BIG(*) AS _ic
FROM   dbo.Articles_in_Consignment
       INNER JOIN dbo.ORDER_D
            ON  dbo.Articles_in_Consignment.Order_dispatchment_id = dbo.ORDER_D.Order_dispatchment_id
       INNER JOIN dbo.ORDERS
            ON  dbo.ORDER_D.Order_dispatchment_id = dbo.ORDERS.Order_dispatchment_id
       INNER JOIN dbo.ORDER_ITEMS
            ON  dbo.ORDERS.Orders_id = dbo.ORDER_ITEMS.Orders_id
       INNER JOIN dbo.ORDER_ALLOCATE
            ON  dbo.ORDER_ITEMS.Order_items_id = dbo.ORDER_ALLOCATE.Order_items_id
       INNER JOIN dbo.STOCKIN
            ON  dbo.ORDER_ALLOCATE.Stockin_id = dbo.STOCKIN.Stockin_id
       INNER JOIN dbo.COMPONENT
            ON  dbo.STOCKIN.Component_id = dbo.COMPONENT.Component_id
WHERE  (dbo.ORDERS.Cold = 'No')
GROUP BY
       dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id 


However,WHEN i TRY TO ADD a subquery following
SELECT statement the resulted ROWS increases TO 155550 row due TO subquery returning one TO many ROWS.

(
               SELECT CASE 
                           WHEN EXISTS (
                                    SELECT *
                                    FROM   dbo.ORDER_ITEMS
                                    WHERE  Component_id IN (1111, 1111)
                                           AND Orders_id = dbo.ORDERS.Orders_id
                                ) THEN CAST(1 AS BIT)
                           ELSE CAST(0 AS BIT)
                      END
           ) AS DG


Ordering by OrderId.

How can I solve this so that my consolidation of first query does not break due to subquery?

Current result I am getting with subquery...

Consignment_id    Order_boxsize   Order_dispatchment_id   Component_weight   _ic   DG

--------------    -------------   --------------------    ----------------   ---  --

125                L                121212                    0.639           21   0
125                L                121212                    0.639           21   0


expected result with consolidation.

Consignment_id    Order_boxsize   Order_dispatchment_id   Component_weight   _ic   DG

--------------    -------------   --------------------    ----------------   ---  --

125                L                121212                    1. 278         42   0
You will have to include DG in your grouping...

Select Consignment_id
       ,Orders_boxsize
       ,Orders_boxweight
       ,Orders_boxlength
       ,Orders_boxwidth
       ,Orders_boxdepth
       ,Order_dispatchment_id
       ,SUM(Component_weight) as Component_weight
       ,SUM(_ic) as _ic
       ,DG
from
(       
SELECT dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id,
       SUM(
           ISNULL(dbo.COMPONENT.Component_weight, 0) * ISNULL(dbo.ORDER_ALLOCATE.Order_allocate_qty, 0)
       ) AS Component_weight,
       COUNT_BIG(*) AS _ic
,(
               SELECT CASE 
                           WHEN EXISTS (
                                    SELECT *
                                    FROM   dbo.ORDER_ITEMS
                                    WHERE  Component_id IN (1111, 1111)
                                           AND Orders_id = dbo.ORDERS.Orders_id
                                ) THEN CAST(1 AS BIT)
                           ELSE CAST(0 AS BIT)
                      END
           ) AS DG       
FROM   dbo.Articles_in_Consignment
       INNER JOIN dbo.ORDER_D
            ON  dbo.Articles_in_Consignment.Order_dispatchment_id = dbo.ORDER_D.Order_dispatchment_id
       INNER JOIN dbo.ORDERS
            ON  dbo.ORDER_D.Order_dispatchment_id = dbo.ORDERS.Order_dispatchment_id
       INNER JOIN dbo.ORDER_ITEMS
            ON  dbo.ORDERS.Orders_id = dbo.ORDER_ITEMS.Orders_id
       INNER JOIN dbo.ORDER_ALLOCATE
            ON  dbo.ORDER_ITEMS.Order_items_id = dbo.ORDER_ALLOCATE.Order_items_id
       INNER JOIN dbo.STOCKIN
            ON  dbo.ORDER_ALLOCATE.Stockin_id = dbo.STOCKIN.Stockin_id
       INNER JOIN dbo.COMPONENT
            ON  dbo.STOCKIN.Component_id = dbo.COMPONENT.Component_id
WHERE  (dbo.ORDERS.Cold = 'No')
GROUP BY
       dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id
) a
Group by
Consignment_id
,Orders_boxsize
,Orders_boxweight
,Orders_boxlength
,Orders_boxwidth
,Orders_boxdepth
,Order_dispatchment_id
,DG