Portal | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
balaji81_k

Active User

Joined: 29 Jun 2005
Posts: 141

Posted: Fri Mar 24, 2017 7:51 pm    Post subject: Getting sum on Totals and Adjusted totals

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?

Nic Clouston

Global Moderator

Joined: 10 May 2007
Posts: 2409
Location: Hampshire, UK

 Posted: Fri Mar 24, 2017 8:01 pm    Post subject: Do your programming in your program, do your data retrieval with SQL. Quicker, clearer, cheaper.
balaji81_k

Active User

Joined: 29 Jun 2005
Posts: 141

 Posted: Fri Mar 24, 2017 9:35 pm    Post subject: Ok Nic , Sure this can be done in programming , thought of checking by any chance it can be done in Query itself.
Rohit Umarjikar

Global Moderator

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

Posted: Sat Mar 25, 2017 3:34 am    Post subject:

 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 ?
balaji81_k

Active User

Joined: 29 Jun 2005
Posts: 141

 Posted: Mon Mar 27, 2017 8:10 am    Post subject: 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
Rohit Umarjikar

Global Moderator

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

Posted: Mon Mar 27, 2017 8:36 pm    Post subject:

 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?
Auryn

New User

Joined: 11 Jan 2006
Posts: 66
Location: Lower Saxony (DE)

Posted: Fri Jun 09, 2017 1:39 pm    Post subject:

Rohit Umarjikar wrote:
 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?

d'accord, there's no aggregate function - like MEDIAN, STDDEV or SUM.
"Wrap" your CASE phrase with a SUM function like
 Code: SELECT   STORE_NBR, STORE_TYPE, DIST_NBR,        , SUM(              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

Otherwise use a sub select, i.e.
 Code: SELECT   sub.STORE_NBR        , sub.STORE_TYPE        , sub.DIST_NBR        , SUM(sub.adjusted_sales)               AS adjusted_sales_total   FROM (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           FROM SALES_STORE          WHERE DEPT_NBR        <> 10            AND STORE_TYPE      IN (P,T)            AND SALES_POST_DATE  = CURRENT DATE        ) sub  GROUP BY sub.STORE_NBR         , sub.STORE_TYPE         , sub.DIST_NBR WITH UR
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

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

 © 2003-2020 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us