Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    5

    Unanswered: Multiply 2 fields then SUM?

    I'm trying to multiply 2 fields to get a "weight shipped" value and then I need to add each "weight shipped" value by "master number". Currently my query looks like:

    SELECT [1.2.7 Order file Detail by SKU Merged].[Document Type]
    , [1.2.7 Order file Detail by SKU Merged].[MASTER NUMBER]
    , [1.2.7 Order file Detail by SKU Merged].[Order Date]
    , (CONVERT(DECIMAL(15,5), [1.2.7 Order file Detail by SKU Merged].[QUANTITY SHIPPED]))*(CONVERT(DECIMAL(15,5), [1.2.2 Product Master File].[SHIPPING WEIGHT LBS])) AS 'Weight Shipped'

    FROM [1.2.7 Order file Detail by SKU Merged] INNER JOIN
    [1.2.2 Product Master File] ON [1.2.7 Order file Detail by SKU Merged].Item = [1.2.2 Product Master File].Item

    GROUP BY [1.2.7 Order file Detail by SKU Merged].[Document Type]
    , [1.2.7 Order file Detail by SKU Merged].[MASTER NUMBER]
    , [1.2.7 Order file Detail by SKU Merged].[Order Date]
    , [1.2.7 Order file Detail by SKU Merged].[QUANTITY SHIPPED]
    , [1.2.2 Product Master File].[SHIPPING WEIGHT LBS]

    This query returns the result:

    Document MASTER NUMBER Order Date Weight Shipped
    Invoice 100000 2010-03-03 00:00:00.000 0.0300000000
    Invoice 100000 2010-03-03 00:00:00.000 0.0200000000
    Invoice 100000 2010-03-03 00:00:00.000 0.0600000000
    Invoice 100002 2010-03-03 00:00:00.000 0.0100000000
    Invoice 100002 2010-03-03 00:00:00.000 0.0300000000
    Invoice 100002 2010-03-03 00:00:00.000 0.0200000000
    Invoice 100002 2010-03-03 00:00:00.000 0.0600000000
    Invoice 100002 2010-03-03 00:00:00.000 0.0200000000
    Invoice 100002 2010-03-03 00:00:00.000 0.0400000000
    Invoice 100002 2010-03-03 00:00:00.000 0.0300000000

    What I'd like to have is all "Weight Shipped" for the "Master Number" '100000' summed so that I see the total weight for "Master Number" '100000' was .2

    Any thoughts? (sorry for the long syntax)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT [1.2.7 Order file Detail by SKU Merged].[Document Type]
         , [1.2.7 Order file Detail by SKU Merged].[MASTER NUMBER]
         , [1.2.7 Order file Detail by SKU Merged].[Order Date]
         , SUM( [1.2.7 Order file Detail by SKU Merged].[QUANTITY SHIPPED] *
                [1.2.2 Product Master File].[SHIPPING WEIGHT LBS] )
            AS 'SUM Weight Shipped'
      FROM [1.2.7 Order file Detail by SKU Merged] 
    INNER 
      JOIN [1.2.2 Product Master File] 
        ON [1.2.2 Product Master File].Item = 
           [1.2.7 Order file Detail by SKU Merged].Item
    GROUP 
        BY [1.2.7 Order file Detail by SKU Merged].[Document Type]
         , [1.2.7 Order file Detail by SKU Merged].[MASTER NUMBER]
         , [1.2.7 Order file Detail by SKU Merged].[Order Date]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    5
    Thanks R937.

    Tweaked it slightly for a decimal conversion, but that worked.
    Last edited by slw2206; 04-07-11 at 13:01.

Posting Permissions

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