|
View previous topic :: View next topic
|
| Author |
Message |
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
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 |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Do your programming in your program, do your data retrieval with SQL. Quicker, clearer, cheaper. |
|
| Back to top |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| 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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005
Posts: 155
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
| 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 |
|
|
|
|
