#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Sum total of formula


    Hi

    I have no idea how I would do this so any help would be great!

    I need to add criteria that will reduce the number of rows returned and subtotal by invoice number

    Formula needed:
    1. (disc1-100)/100 as DS
    2. (qty_inv * price) as VAL
    3. (DS * VAL) as SALES

    And then subtotal all by SALES and invoice number

    Here is my code

    Code:
    SELECT "ainvdet_CurrentMonth"."inv_account", "ainvdet_CurrentMonth"."invoice_date", "ainvdet_CurrentMonth"."qty_inv", 
    "ainvdet_CurrentMonth"."nom_sale", "ainvdet_CurrentMonth"."invoice",
    "ainvdet_CurrentMonth"."price", "ainvdet_CurrentMonth"."disc1", "ainvdet_CurrentMonth"."description", "ainvdet_CurrentMonth"."part", "stock"."main_supplier", 
    "stock"."price_family",
    "ainvdet_CurrentMonth"."product_group", "ainvhead_CurrentMonth"."user_created", "ainvhead_CurrentMonth"."salesman",
    "ainvhead_CurrentMonth"."ref"
     FROM   ("mbs01"."public"."ainvdet.CurrentMonth" "ainvdet_CurrentMonth" 
     LEFT OUTER JOIN "mbs01"."public"."ainvhead.CurrentMonth" "ainvhead_CurrentMonth" 
     ON (("ainvdet_CurrentMonth"."company"="ainvhead_CurrentMonth"."company") 
     AND ("ainvdet_CurrentMonth"."ainvheadid"="ainvhead_CurrentMonth"."ainvheadid")) 
     AND ("ainvdet_CurrentMonth"."depot"="ainvhead_CurrentMonth"."depot")) 
     LEFT OUTER JOIN "mbs01"."public"."stock" "stock" 
     ON (("ainvdet_CurrentMonth"."company"="stock"."company") 
     AND ("ainvdet_CurrentMonth"."depot"="stock"."depot")) 
     AND ("ainvdet_CurrentMonth"."stockid"="stock"."stockid")
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0
    This may help a bit more on what I need to do:

    I want to be able to total on the result of 'ds * val' and group by .invoice

    Code:
    SELECT 
      "ainvdet.CurrentMonth".invoice_date, 
      "ainvdet.CurrentMonth".invoice, 
      ("ainvdet.CurrentMonth".qty_inv * "ainvdet.CurrentMonth".price) as val,
      (("ainvdet.CurrentMonth".disc1-100)/100) as ds,
      left("ainvhead.CurrentMonth".salesman,2) as salesmanid,
      left("ainvhead.CurrentMonth".user_created::text,2) as userid,  
      "ainvdet.CurrentMonth".layout_section
    FROM 
      public."ainvdet.CurrentMonth", 
      public."ainvhead.CurrentMonth"
    WHERE 
      "ainvdet.CurrentMonth".company = "ainvhead.CurrentMonth".company AND
      "ainvdet.CurrentMonth".ainvheadid = "ainvhead.CurrentMonth".ainvheadid AND "ainvdet.CurrentMonth".layout_section= 'D'
    ORDER BY userid asc;

IMN logo majestic logo threadwatch logo seochat tools logo