IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Getting sum on Totals and Adjusted totals


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 141

PostPosted: Fri Mar 24, 2017 7:51 pm
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: 2449
Location: Hampshire, UK

PostPosted: Fri Mar 24, 2017 8:01 pm
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

Active User


Joined: 29 Jun 2005
Posts: 141

PostPosted: Fri Mar 24, 2017 9:35 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2569
Location: NYC,USA

PostPosted: Sat Mar 25, 2017 3:34 am
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

Active User


Joined: 29 Jun 2005
Posts: 141

PostPosted: Mon Mar 27, 2017 8:10 am
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2569
Location: NYC,USA

PostPosted: Mon Mar 27, 2017 8:36 pm
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
Auryn

New User


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

PostPosted: Fri Jun 09, 2017 1:39 pm
Reply with quote

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


Read more: ibmmainframes.com/viewtopic.php?t=65837#ixzz4jUQxLr2d[/code]
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts count totals DFSORT/ICETOOL 7
No new posts Building totals DFSORT/ICETOOL 0
No new posts Create totals from negative and posit... DFSORT/ICETOOL 3
No new posts Sum the hexadecimal input and diplay ... JCL & VSAM 15
No new posts Merging records and calculating TOTAL... SYNCSORT 44
Search our Forums:

Back to Top