Sum total of formula

Dev Shed Forums Sponsor:

October 23rd, 2012, 07:47 AM

dibblejon

Registered User

Join Date: Jun 2012

Posts: 17

Time spent in forums: 3 h 48 m 31 sec
Reputation 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")

October 23rd, 2012, 09:14 AM

dibblejon

Registered User

Join Date: Jun 2012

Posts: 17

Time spent in forums: 3 h 48 m 31 sec
Reputation 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;

Viewing: Dev Shed Forums > Databases > PostgreSQL Help > Sum total of formula

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump