Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002

    Unhappy Unanswered: Re: Left function causing Sum function not working???

    Hi all,

    My query won't add up the quantities after adding LEFT function in SQL statement can anyone see why?

    SELECT dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, SUM(dbo.tblBag_data.bag_quantity) AS qty_on_hand,
    dbo.tblShipping_sched.cust_num, dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num,
    FROM dbo.tblShipping_sched LEFT OUTER JOIN
    dbo.tblBag_data ON dbo.tblShipping_sched.work_ord_line_num = dbo.tblBag_data.work_ord_line_num AND
    LEFT(dbo.tblShipping_sched.work_ord_num, 6) = LEFT(dbo.tblBag_data.work_ord_num, 6)
    WHERE (dbo.tblShipping_sched.work_ord_num LIKE '343024%')
    GROUP BY dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, dbo.tblBag_data.bag_quantity, dbo.tblShipping_sched.cust_num,
    dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num

    ''this s/b one record w/ summed qty
    Work_order line_num qty_on_hand cust_num cust a_p_n a_c_n
    343024-01 001 16540 32246 Davol Inc R1082061 R00295-059-70EPF-HM+
    343024-01 001 27344 32246 Davol Inc R1082061 R00295-059-70EPF-HM+

    Thanks for your input.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    because your GROUP BY specifies the individual dbo.tblBag_data.bag_quantity values | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts