Is is possible to use Aggregate functions inside CASE statement?

Database

Is is possible to use Aggregate functions inside CASE statement?

I want to execute SUM of a field at different grouping levels... is it possible to use SUM inside CASE statement???

Something like below..

SELECT ITEM_ID
, LOC_ID
, MKT_NBR
, SLS_YR_WK_KEY
, SLS_YEAR
, SLS_WK
, SLS_MNTH
, MUS_VLCTY_IND
, CASE WHEN MUS_VLCTY_IND = 0 THEN SUM(SLS_QTY) OVER( PARTITION BY ITEM_ID , MKT_NBR,SLS_YR_WK_KEY)
ELSE SUM(SLS_QTY) OVER( PARTITION BY ITEM_ID , LOC_ID,SLS_YR_WK_KEY)
END AS AGG_SLS_QTY
FROM SNLTY_TEMP1_V
30 REPLIES
Junior Contributor

Re: Is is possible to use Aggregate functions inside CASE statement?

This query is sytactically correct and should run without problems.

Dieter

Re: Is is possible to use Aggregate functions inside CASE statement?

SELECT ITEM_ID
, LOC_ID
, MKT_NBR
, SLS_YR_WK_KEY
, SLS_YEAR
, SLS_WK
, SLS_MNTH
, MUS_VLCTY_IND
, Sum(CASE WHEN MUS_VLCTY_IND = 0 THEN SLS_QTY OVER( PARTITION BY ITEM_ID , MKT_NBR,SLS_YR_WK_KEY)
ELSE SLS_QTY OVER( PARTITION BY ITEM_ID , LOC_ID,SLS_YR_WK_KEY)
END AS AGG_SLS_QTY)
FROM SNLTY_TEMP1_V

Sum( Case Condition I s possible here) ok

Re: Is is possible to use Aggregate functions inside CASE statement?

SELECT ITEM_ID
, LOC_ID
, MKT_NBR
, SLS_YR_WK_KEY
, SLS_YEAR
, SLS_WK
, SLS_MNTH
, MUS_VLCTY_IND
, Sum(CASE WHEN MUS_VLCTY_IND = 0 THEN SLS_QTY OVER( PARTITION BY ITEM_ID , MKT_NBR,SLS_YR_WK_KEY)
ELSE SLS_QTY OVER( PARTITION BY ITEM_ID , LOC_ID,SLS_YR_WK_KEY)
END AS AGG_SLS_QTY)
FROM SNLTY_TEMP1_V

Sum( Case Condition I s possible here) ok
Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

sum(case condition) and group by the columns will work
Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi

I have a qyery:

Below is the resultset from the query









DC HS cuscode cuname Lvl1cd Year_ sales
1800 1400 1,023 AMRUTH DHARA INDUSTRIES 7 2011 919
1800 1400 1,079 D T D C COURIERS CARGO LTD 5 2012 16702
1800 1400 1,092 APOORVA IYENGAR'S BAKERY 3 2011 4011
1800 1400 1,094 SIDDARTHA STEEL & IRON 7 2011 3214
1800 1400 1,094 SIDDARTHA STEEL & IRON 7 2012 5787
1800 1400 1,100 ANAND FLOORINGS 7 2012 4791
1800 1400 1,121 MADHU SWEETS 1 2012 1432
1800 1400 1,125 MAHAVEER MEDICALS 3 2011 8265
1800 1400 1,131 G G WELLING 5 2012 5663
1800 1400 1,134 VIJAYA HAMSA STORES 3 2012 7090
1800 1400 1,140 MOHAN BHANDAR DEPARTMENTAL STORE 3 2011 161983

I want the resultset as below:









DC HS cuscode cuname Lvl1cd 2011 2012
1800 1400 1,023 AMRUTH DHARA INDUSTRIES 7 919  
1800 1400 1,079 D T D C COURIERS CARGO LTD 5   16702
1800 1400 1,092 APOORVA IYENGAR'S BAKERY 3 4011  
1800 1400 1,094 SIDDARTHA STEEL & IRON 7 3214 5787
1800 1400 1,100 ANAND FLOORINGS 7   4791
1800 1400 1,121 MADHU SWEETS 1   1432
1800 1400 1,125 MAHAVEER MEDICALS 3 8265  
1800 1400 1,131 G G WELLING 5   5663
1800 1400 1,134 VIJAYA HAMSA STORES 3   7090
1800 1400 1,140 MOHAN BHANDAR DEPARTMENTAL STORE 3 161983  
TTL         178392 41465

Can anyone give a solution to this

Thanks

Kalyan S R

Junior Contributor

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi Kalyan,

it's time for CASE pivot table :-)

When "sales" is calculated as SUM(blabla), you just have to replace it with

SUM(CASE WHEN year_sales = 2011 THEN blabla END) AS "2011",

SUM(CASE WHEN year_sales = 2012 THEN blabla END) AS "2012"

This will return NULL for non-existing, if you want 0 then add "ELSE 0" to the CASE.

Dieter

Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi

Thanks a lot for the response.

My doubt is

substr(ltrim(a.month_id),1,4) as Saal, (in the table it is like 201105,201205..so on...)

          which is givng the output as 2011,2012 etc in the smme coloumn which I want to split

sum (a.net_Sales)

The below did'nt work....

SUM(CASE WHEN Saal ='2011' THEN (a.net_sales) Else 0,end) AS "2011",

SUM(CASE WHEN Saal ='2012' THEN (a.net_sales) Else 0,end) AS "2012"

Can you suggest?

Thanks

Kalyan S R

Junior Contributor

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi Kalyan,

you have to move the calculation into the CASE and remove the Saal column:

select DC, HS, cuscode, cuname, Lvl1cd

  SUM(CASE WHEN substr(ltrim(a.month_id),1,4)='2011' THEN (a.net_sales) Else 0,end) AS "2011",

  SUM(CASE WHEN substr(ltrim(a.month_id),1,4)='2012' THEN (a.net_sales) Else 0,end) AS "2012"

from tab

group by 1,2,3,4,5

Dieter

Enthusiast

Re: Is is possible to use Aggregate functions inside CASE statement?

Hi,

Thanks I got it, Another issue now, in the below query if i add one or two mnths more, one or two stores more I get a "2646 Spool space error' Can we optimise the query to achieve this result?

select

a.st as DC,

b.hs as HS,

b.cus_no as Cuscode,

b.Cus_nm as Customername,

c.cus_assort_se_id,

d.cust_ass_se_desc as Lvl1desc,

sum (case substr(ltrim(a.m_id),1,4) when '2011' then ( a.cu_net_val_nsp) else 0 end) as "Year2011",

sum (case substr(ltrim(a.m_id),1,4) when '2012' then ( a.cu_net_val_nsp) else 0 end) as "Year2012"

from indccp_dwh_views.dw_v_cu_in a,

indccp_dwh_views.dw_v_cus b,

indccp_dwh_views.dw_v_cust_brh c,

indccp_dwh_views.dw_v_cust_brnh_family d

where a.month_id in (201205,201105)

and a.St in (10)

and a.hs_id||a.cus_no=b.home_store_id||b.cus_no

and c.brh_id=b.brh_id

and c.cust_asrt_section_id=d.cust_asrt_section_id

group by a.store_id,b.hs,b.cust_no,b.cus_name,c.cust_asrt_section_id,cust_asrt_section_desc

order by 1,2;

Thanks'

Kalyan S R