Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Jan 18 '13 at 15:36

This question came from our site for professional and enthusiast programmers.

    
Which is your RDBMS? –  techdo Jan 16 '13 at 6:45
    
Sorry I am new to stack exchange , dint notice your comment . I am using Microsoft SQL server. I have also edited my question. –  wcraft Jan 17 '13 at 6:28
    
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 Jan 18 '13 at 11:47
    
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 ... –  ypercube Jan 18 '13 at 12:57
add comment

3 Answers 3

up vote 1 down vote accepted

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
share|improve this answer
    
Getting error : cannot use an aggregate or a subquery in an expression used for the group by by list of a group by clause –  wcraft Jan 18 '13 at 9:34
    
Sorry :-( ... I did an edit... –  bummi Jan 18 '13 at 10:02
    
You saved my life !! :) –  wcraft Jan 18 '13 at 10:39
add comment

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
share|improve this answer
    
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. –  wcraft Jan 16 '13 at 21:29
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.