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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy