Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Getting sum on Totals and Adjusted totals

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
balaji81_k

New User


Joined: 29 Jun 2005
Posts: 89

PostPosted: Fri Mar 24, 2017 7:51 pm    Post subject: Getting sum on Totals and Adjusted totals
Reply with quote

Hi Team ,
I am looking to get the sum of sales amount by store and district . Certain district allows some adjustment in their totals. I need to include the logic to know actual sum of Net_Sales_amt + adjusted Net sales_amt by Store and Dist .
Totals = Actual totals + Adjusted Totals .

Code:

SELECT STORE_NBR,STORE_TYPE,DIST_NBR,
CASE WHEN DISTRICT = 10 OR DISTRICT  = 11
      THEN ROUND(SALES_AMT * 100  * 1.013,0)
     WHEN DISTRICT = 12 OR DISTRICT =  13
       THEN ROUND(SALES_AMT * 100 * 1.016,0)
ELSE
        ROUND(SALES_AMT*100,0)
END AS ADJUSTED_SALES_TOTAL
FROM SALES_STORE
WHERE DEPT_NBR  <> 10
AND STORE_TYPE  IN(P,T)
AND SALES_POST_DATE = CURRENT DATE
GROUP BY STORE_NBR, STORE_TYPE,DIST_NBR
WITH UR 
 


Can any one help me to get me to the Totals Sum = Actuals + Adjsuted?
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1788
Location: UK

PostPosted: Fri Mar 24, 2017 8:01 pm    Post subject:
Reply with quote

Do your programming in your program, do your data retrieval with SQL. Quicker, clearer, cheaper.
Back to top
View user's profile Send private message
balaji81_k

New User


Joined: 29 Jun 2005
Posts: 89

PostPosted: Fri Mar 24, 2017 9:35 pm    Post subject:
Reply with quote

Ok Nic , Sure this can be done in programming , thought of checking by any chance it can be done in Query itself.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1696
Location: NY,USA

PostPosted: Sat Mar 25, 2017 3:34 am    Post subject:
Reply with quote

Quote:
Can any one help me to get me to the Totals Sum = Actuals + Adjsuted?

You did not tell us what is not working ? where is the SUM used ?
Back to top
View user's profile Send private message
balaji81_k

New User


Joined: 29 Jun 2005
Posts: 89

PostPosted: Mon Mar 27, 2017 8:10 am    Post subject:
Reply with quote

Hi Rohit,

The Query posted is working and i want to have an idea that if i can get total sum(sales + adjusted sales) group by store_nbr across all dist .
If i use SUM after case statement it doesn't work and throws error
SQLCODE = -122 not a valid context.

Thanks
Balaji K
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1696
Location: NY,USA

PostPosted: Mon Mar 27, 2017 8:36 pm    Post subject:
Reply with quote

Quote:
If i use SUM after case statement it doesn't work and throws error
SQLCODE = -122 not a valid context.
Did you try using SUM before CASE?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts count totals anatol DFSORT/ICETOOL 7 Mon Aug 25, 2014 11:41 pm
No new posts Building totals oerdgie DFSORT/ICETOOL 0 Mon May 12, 2014 6:01 pm
No new posts Create totals from negative and posit... anatol DFSORT/ICETOOL 3 Thu Oct 03, 2013 3:16 am
No new posts Sum the hexadecimal input and diplay ... to_sreeni JCL & VSAM 15 Wed Jul 10, 2013 5:34 pm
No new posts Merging records and calculating TOTAL... venkata.ravi SYNCSORT 44 Mon Mar 25, 2013 12:34 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us