November 11, 2008 at 3:27 am
Hello,
I really need help with this querysyntax
Example Data:
Table1
number location quantity
23 RED 2
23 RED 6
Table2
number location quantity param1 param2
23 RED 1 5 Order
23 RED 2 6 Order
I want to sum(quantity) in table1 and subtract that with sum(quantity) in table2 and then group by location and number where table2.param1 > 0 and table2.param2 = 'Order'
Desired output from exampledata above would be:
number location quantity
23 RED 5
Is this even possible?
Regards
November 11, 2008 at 4:10 am
select (temp1.qty1-temp2.qty2)as Total from
(select sum(qty)as qty1 from t1
group by Number,location) temp1,
(select sum(qty)as qty2 from t2
where P1>0 and P2='Order'
group by Number,location) temp2
please let me know it doesn't suit your requirement.
November 11, 2008 at 4:12 am
SELECT T1.location, T1.number, T1.quantity - T2.quantity AS quantity
FROM
(
    SELECT T11.location, T11.number, SUM(T11.quantity) AS quantity
    FROM Table1 T11
    GROUP BY T11.location, T11.number
) T1
    JOIN
    (
        SELECT T21.location, T21.number, SUM(T21.quantity) AS quantity
        FROM Table2 T21
        WHERE T21.param1 > 0
            AND T21.param2 = 'Order'
        GROUP BY T21.location, T21.number
    ) T2
        ON T1.location = T2.location
            AND T1.number = T2.number
November 11, 2008 at 5:33 am
Ok this is great stuff but if I add this row to table1
location number quantity
BLUE 10 4
...then it doesnt calculate correct.
the result is still
location number quantity
RED 23 5
November 11, 2008 at 5:50 am
please check this...
this is giving both the rows
select temp1.location,temp1.number,(temp1.qty1-temp2.qty2)as Total from
(select location,number,sum(quantity)as qty1 from test1
group by Number,location) temp1,
(select sum(quantity)as qty2 from test11
where param1>0 and param2='Ord'
group by Number,location) temp2
November 11, 2008 at 6:24 am
No, the query then returns
BLUE 10 1
It should be
BLUE 10 4
November 11, 2008 at 6:29 am
Use an OUTER JOIN and use ISNULL or COALESCE on the outer quantity. eg
SELECT T1.location, T1.number, T1.quantity - ISNULL(T2.quantity, 0) AS quantity
FROM
(
    SELECT T11.location, T11.number, SUM(T11.quantity) AS quantity
    FROM Table1 T11
    GROUP BY T11.location, T11.number
) T1
    LEFT JOIN
    (
        SELECT T21.location, T21.number, SUM(T21.quantity) AS quantity
        FROM Table2 T21
        WHERE T21.param1 > 0
            AND T21.param2 = 'Order'
        GROUP BY T21.location, T21.number
    ) T2
        ON T1.location = T2.location
            AND T1.number = T2.number
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply