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")