Subtotal on formula and group by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subtotal on formula and group by

Author

Topic

dibblejon
Starting Member

36 Posts

Posted - 2012-10-23 : 09:39:09

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

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

dibblejon
Starting Member

36 Posts

Posted - 2012-10-23 : 10:16:15

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

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-23 : 18:34:30

its looks like straightforward grouping scenario. Have a look at GROUP BY and SUM() in books online

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dibblejon
Starting Member

36 Posts

Posted - 2012-10-24 : 05:14:13

My issue is where I try and use sum on the totals created by my formula I get unknown field errors :

eg

SELECT 
  "ainvdet.CurrentMonth".invoice_date, 
  "ainvdet.CurrentMonth".invoice, 
  ("ainvdet.CurrentMonth".qty_inv * "ainvdet.CurrentMonth".price) as val,
  (("ainvdet.CurrentMonth".disc1-100)/100) as ds,
  sum (ds * val),
  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;

I get this error :
ERROR: column "ds" does not exist
LINE 6: sum (ds * val),

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-10-24 : 06:21:33

Yeah unfortunately you can't use the alias DS so just copy & paste the formula into the expression.

dibblejon
Starting Member

36 Posts

Posted - 2012-10-24 : 07:09:38

Thanks - have got a bit further

Issue though I have is that it is not totalling by the invoice column - where am I going wrong?

SELECT 
  "ainvdet.CurrentMonth".invoice, 
  "ainvdet.CurrentMonth".invoice_date,          
  "ainvdet.CurrentMonth".product_group,
  "ainvdet.CurrentMonth".nom_sale,
  "ainvdet.CurrentMonth".del_account,
  SUM(((100 ::numeric - "ainvdet.CurrentMonth".disc1) / 100 ::numeric) * ("ainvdet.CurrentMonth".qty_inv * "ainvdet.CurrentMonth".price)) AS sales,
  "left"("ainvhead.CurrentMonth".salesman ::text, 2) AS salesmanid,
  "left"("ainvhead.CurrentMonth".user_created ::text, 2) AS userid,
  "ainvdet.CurrentMonth".layout_section,
  "ainvhead.CurrentMonth".ref
FROM
  "ainvdet.CurrentMonth",
  "ainvhead.CurrentMonth"
WHERE
  "ainvdet.CurrentMonth".company = "ainvhead.CurrentMonth".company AND 
  "ainvdet.CurrentMonth".ainvheadid = "ainvhead.CurrentMonth".ainvheadid AND 
  "ainvdet.CurrentMonth".layout_section ::text = 'D' ::text AND 
  "ainvdet.CurrentMonth".nom_sale = 25001 ::numeric
GROUP BY
  "ainvdet.CurrentMonth".invoice,
  "ainvdet.CurrentMonth".invoice_date,
  "ainvdet.CurrentMonth".product_group,
  "ainvdet.CurrentMonth".nom_sale,
  "ainvdet.CurrentMonth".del_account,
  salesmanid,
  userid,
  "ainvdet.CurrentMonth".layout_section,
  "ainvhead.CurrentMonth".ref
ORDER BY
  "ainvdet.CurrentMonth".invoice_date,
  "ainvdet.CurrentMonth".invoice

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 09:25:06

is this T-SQL? i dont think ::text , etc are valid syntax

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dibblejon
Starting Member

36 Posts

Posted - 2012-10-24 : 09:34:54

It is postgres sql - but the principle is very similar. Problem is that not many people on postgres forums.... Hope I have not caused offence.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-24 : 10:13:55

quote:
Originally posted by dibblejon

It is postgres sql - but the principle is very similar. Problem is that not many people on postgres forums.... Hope I have not caused offence.

Not at all
but question is whether suggestion we give will work in postgre. there're not too many people who know postgre here

in T-SQL we do it like


SELECT DISTINCT
  "ainvdet.CurrentMonth".invoice, 
  "ainvdet.CurrentMonth".invoice_date,          
  "ainvdet.CurrentMonth".product_group,
  "ainvdet.CurrentMonth".nom_sale,
  "ainvdet.CurrentMonth".del_account,
  SUM(((100 ::numeric - "ainvdet.CurrentMonth".disc1) / 100 ::numeric) * ("ainvdet.CurrentMonth".qty_inv * "ainvdet.CurrentMonth".price)) OVER (PARTITION BY "ainvdet.CurrentMonth".invoice) AS sales,
  "left"("ainvhead.CurrentMonth".salesman ::text, 2) AS salesmanid,
  "left"("ainvhead.CurrentMonth".user_created ::text, 2) AS userid,
  "ainvdet.CurrentMonth".layout_section,
  "ainvhead.CurrentMonth".ref
FROM
  "ainvdet.CurrentMonth",
  "ainvhead.CurrentMonth"
WHERE
  "ainvdet.CurrentMonth".company = "ainvhead.CurrentMonth".company AND 
  "ainvdet.CurrentMonth".ainvheadid = "ainvhead.CurrentMonth".ainvheadid AND 
  "ainvdet.CurrentMonth".layout_section ::text = 'D' ::text AND 
  "ainvdet.CurrentMonth".nom_sale = 25001 ::numeric
ORDER BY
  "ainvdet.CurrentMonth".invoice_date,
  "ainvdet.CurrentMonth".invoice

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dibblejon
Starting Member

36 Posts

Posted - 2012-10-25 : 04:11:04

Thank you for looking to help.

Your query runs but we do not get the distinct total by invoice number as required. I will have to read up more on the postgres version of SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources