# Getting sum on Totals and Adjusted totals

Author Message
balaji81_k

Active User

Joined: 29 Jun 2005
Posts: 141

Posted: Fri Mar 24, 2017 7:51 pm

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: 2455
Location: Hampshire, UK

 Posted: Fri Mar 24, 2017 8:01 pm 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 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: 2632
Location: NYC,USA

Posted: Sat Mar 25, 2017 3:34 am

 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 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: 2632
Location: NYC,USA

Posted: Mon Mar 27, 2017 8:36 pm

 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: 67
Location: Lower Saxony (DE)

Posted: Fri Jun 09, 2017 1:39 pm

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
 View Bookmarks All times are GMT + 6 Hours

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