October 28, 2003 at 9:02 am
I need help with a complex SQL statement (complex to me anyway).
I need to find a total. I can get there in two statements, however, the solution has to be from a single SQL statement. So here go's:
The total I am after is result of statement 1 - result of statement 2
Statement 1 = Select Distinct part, Sum(qty), locationTo from parts where part = 'A' and locationTo = 1
Statement 2 = Select Distinct part, Sum(qty), locationFrom from parts where part = 'A' and locationFrom = 1
I would be extremely grateful for a solution
Regards
October 28, 2003 at 9:22 am
SELECT SUM(CASE WHEN LocationTo = 1 THEN Qty ELSE 0 END) - SUM(CASE WHEN LocationFrom = 1 THEN Qty ELSE 0 END)
FROM Parts
WHERE Part = 'A'
--Jonathan
--Jonathan
October 28, 2003 at 10:19 am
Alternative solution using (self)joins :
Select P1.part,
P1.Result - P2.Result As Result,
P1.locationTo,
P2.locationFrom
FROM (SELECT part, locationTo,
sum(qty) as Result
FROM parts
WHERE locationTo = 1
GROUP BY part, locationTo) P1
INNER JOIN
(SELECT part, locationFrom,
sum(qty) as Result
FROM parts
WHERE locationFrom = 1
GROUP BY part, locationFrom) P2
ON P1.part = P2.part
This will give you the 'result' for each value of part. Add a where clause in the subselect in the FROM if you want to limit it further to only part 'A'.
October 28, 2003 at 11:40 am
Here's another option, I think...
SELECT part,
SUM(qty)
FROM parts
WHERE (part = 'A' AND locationTo =1) OR
(part='A' AND locationFROM = 1)
GROUP BY part
Cheers
October 29, 2003 at 2:28 am
select A.part, Sum(A.Qty) - Sum(B.Qty)
from parts A,
parts B
where A.part = B.Part
A.Part = 'A'
A.locationTo = B.locationFrom
A.locationTo = 1
group by A.part,B.part
quote:
I need help with a complex SQL statement (complex to me anyway).I need to find a total. I can get there in two statements, however, the solution has to be from a single SQL statement. So here go's:
The total I am after is result of statement 1 - result of statement 2
Statement 1 = Select Distinct part, Sum(qty), locationTo from parts where part = 'A' and locationTo = 1
Statement 2 = Select Distinct part, Sum(qty), locationFrom from parts where part = 'A' and locationFrom = 1
I would be extremely grateful for a solution
Regards
October 29, 2003 at 4:01 am
Thanks for the solutions. However, I am still having difficulty because I did not realise that there was a third Sum to take into account. I've tried adapting the solution given by NPeeters however I getting syntax errors. Here is the modified script:
Select P1.StockRef, P1.Result - P2.Result - P3.Result As Result, P1.locationTo, p2.locationFrom
FROM (SELECT StockRef, locationTo, sum(quantity) as Result
FROM Moves
GROUP BY StockRef, locationTo) P1 INNER JOIN
(SELECT StockRef, locationFrom, sum(quantity) as Result
FROM moves
GROUP BY StockRef, locationFrom) P2 ON P1.StockRef = P2.StockRef INNER JOIN
(SELECT StockRef, locationID, sum(Adjusts) as Result
FROM Adjustments
GROUP BY StockRef, locationID) P3 ON P2.StockRef = P3.StockRef
Where P1.locationTo = P2.locationFrom And
P1.locationTo = P3.LocationID
Thanks in advance
October 29, 2003 at 6:45 am
Are you looking for more than just the computed number to be returned? Do you need the part number, etc. returned also? Can you give an example rowset of what you expect the answer to be?
quote:
I need help with a complex SQL statement (complex to me anyway).I need to find a total. I can get there in two statements, however, the solution has to be from a single SQL statement. So here go's
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply