SQL Subquery , One to Multiple rows

Question

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

• techdo: Which is your RDBMS?
• wcraft: Sorry I am new to stack exchange , dint notice your comment . I am using Microsoft SQL server. I have also edited my question.
• ypercubeᵀᴹ: Let me get this straight: You added an inline subquery (in the SELECT list of your query) and you had more rows in the result than previously?
• ypercubeᵀᴹ: I seriously doubt that the query you propose (and the accepted one) would run without syntax error. It would show something like Column 'dbo.ORDERS.Orders_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause ...

Answer 1

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

• wcraft: Getting error : cannot use an aggregate or a subquery in an expression used for the group by by list of a group by clause
• bummi: Sorry :-( ... I did an edit...
• wcraft: You saved my life !! :)

Answer 2

Your inner query is returning multiple items on some orders. You can fix this by doing:

,(SELECT CASE WHEN EXISTS (
 SELECT DISTINCT Orders_id 
 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 testbool

• wcraft: Thanks for your answer , I think the issue is consolidation does not work properly. For example : (without subquery) Component_weight for one consignment is 1.200 and _ic is 42 .But (with subquery) same row splits in to two with component_weight to 0.6 ,0.6 and _ic to 21, 21. Due to subquery my consolidations gets messed up.

Answer 3

Remove from grouping list the columns that have different values within a group. You have 8 such columns, but the example shows only 4.