July 20, 2005 at 11:14 am
Hi all,
I'm having trouble to the SUM function return the correct value in Select statement. I'm trying to sum the bag_quantity field by apple_part_num field but it adds the sum of the bag_quantity twice. I'm using AC2K, SQL Server 2K
Here is my select statement
SELECT dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num, dbo.tblShipping_sched.qty_on_hand,
dbo.tblShipping_sched.qty_committed, SUM(dbo.tblBag_data.bag_quantity) AS sampling_inven
FROM dbo.tblBag_data INNER JOIN
dbo.tblShipping_sched ON dbo.tblBag_data.apple_part_num = dbo.tblShipping_sched.apple_part_num
GROUP BY dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num, dbo.tblShipping_sched.qty_on_hand,
dbo.tblShipping_sched.qty_committed
apple_part_num apple_catalog_num qty_on_hand qty_committed sampling_inven
R1003636 R00081-109-60EPB-HM+ 12773 925000 1168
R1060833 R00362-104-S3304-HM+ 0 470000 1636
R1079330 R00319-028-70BIL-HM+ 26745 350000 94
S1087696 S(03819283) HM+ 0 4005 100
If I use this query it returns the correct sampling_inven values
SELECT dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num, dbo.tblShipping_sched.qty_on_hand,
dbo.tblShipping_sched.qty_committed, SUM(dbo.tblBag_data.bag_quantity) AS sampling_inven, dbo.tblShipping_sched.work_ord_num,
dbo.tblShipping_sched.work_ord_line_num
FROM dbo.tblBag_data INNER JOIN
dbo.tblShipping_sched ON dbo.tblBag_data.apple_part_num = dbo.tblShipping_sched.apple_part_num
GROUP BY dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num, dbo.tblShipping_sched.qty_on_hand,
dbo.tblShipping_sched.qty_committed, dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num
apple_part_num apple_catalog_num qty_on_hand qty_committed sampling_inven work_ord_num work_ord_line_num
R1003636 R00081-109-60EPB-HM+ 12773 925000 1168 332825-53 002
R1060833 R00362-104-S3304-HM+ 0 470000 818 351703-00 001
R1060833 R00362-104-S3304-HM+ 0 470000 818 351703-00 002
R1079330 R00319-028-70BIL-HM+ 26745 350000 47 346577-12 001
R1079330 R00319-028-70BIL-HM+ 26745 350000 47 350111-05 001
S1087696 S(03819283) HM+ 0 4005 50 351241-02 001
S1087696 S(03819283) HM+ 0 4005 50 351241-02 002
but I need the result to be
apple_part_num apple_catalog_num qty_on_hand qty_committed sampling_inven work_ord_num work_ord_line_num
R1003636 R00081-109-60EPB-HM+ 12773 925000 1168 332825-53 002
R1060833 R00362-104-S3304-HM+ 0 470000 818 351703-00 002
R1079330 R00319-028-70BIL-HM+ 26745 350000 47 350111-05 001
S1087696 S(03819283) HM+ 0 4005 50 351241-02 002
I appreciate your assistance.
July 20, 2005 at 9:10 pm
Hi AJ,
Hope this helps u to get the desired resultset:
--***************************************************
SELECT distinct
s.apple_part_num, s.apple_catalog_num,
s.qty_on_hand,s.qty_committed,
SUM(d.bag_quantity) AS sampling_inven,
,(select max(work_ord_num) from dbo.tblShipping_sched where apple_part_num=s.apple_part_num) as work_ord_num
,(select max(work_ord_line_num) from dbo.tblShipping_sched where apple_part_num=s.apple_part_num)as work_ord_line_num
FROM dbo.tblShipping_sched s INNER JOIN
dbo.tblBag_data
ON dbo.tblBag_data.apple_part_num = s.apple_part_num
GROUP BY s.apple_part_num, s.apple_catalog_num,
s.qty_on_hand,
s.qty_committed,
s.work_ord_num,
s.work_ord_line_num
--***************************************************
July 21, 2005 at 7:39 am
Thanks dilip!!!
It's working now.
It makes sense after looking at your Select statement. But I'm still need to do some studying on GROUP BY.
Thank you again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply