I want to execute SUM of a field at different grouping levels... is it possible to use SUM inside CASE statement???
Something like below..
, 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
This query is sytactically correct and should run without problems.
, 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)
Sum( Case Condition I s possible here) ok
sum(case condition) and group by the columns will work
I have a qyery:
Below is the resultset from the query
AMRUTH DHARA INDUSTRIES
D T D C COURIERS CARGO LTD
APOORVA IYENGAR'S BAKERY
SIDDARTHA STEEL & IRON
G G WELLING
VIJAYA HAMSA STORES
MOHAN BHANDAR DEPARTMENTAL STORE
I want the resultset as below:
Can anyone give a solution to this
Kalyan S R
Kalyan S R, India, Bangalore
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.
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
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?
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"
group by 1,2,3,4,5
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?
a.st as DC,
b.hs as HS,
b.cus_no as Cuscode,
b.Cus_nm as Customername,
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,
where a.month_id in (201205,201105)
and a.St in (10)
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;
first you should change this join on concatenated columns:
to an ANDed join condition:
to get a better estimation of the number of rows. Check if Explain changes.
You might also try to aggregate as early as possible, i.e. move the SUM into a Derived Table and join this to the other tables. This avoids creating a huge intermediate result set before doing the aggregation.
In best case it will look like this:
a.st AS DC,
b.hs AS HS,
b.cus_no AS Cuscode,
b.Cus_nm AS Customername,
d.cust_ass_se_desc AS Lvl1desc,
a.st AS DC,
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
WHERE a.month_id IN (201205,201105)
AND a.St IN (10)
GROUP BY 1,2,3
WHERE a.hs_id =b.home_store_id AND a.cus_no=b.cus_no
ORDER BY 1,2;
You might have to add another SUM(YEAR2011/YEAR2012) in the outer table, based on your PK/FK relations.
Btw, those database names sound familiar to me :-)
OK thanks......a lot, but in the above query it is asking for union function
Another Issue,, The below syntax i feel it is wrong...pls
And function in sum (case)
sum((Case when a11.fn_c in ('nl') and case when a11.cu_sell_v_np in(> 968680.0) then a11.cu_sell_v_np else NULL end)) Nl_sales,
so on...............two more conditions, I am doing this to avoid the Having Clause in the Group By.
When i shoot the below query it says a syntax error......3706 data type m_id dos not match defined type name
CASE WHEN substr(ltrim(m_id),1,4) in ('2011') THEN SUM(cu_s_v_np) OVER( PARTITION BY ,ste_id,he_ste_id,cu_no) else 0 end) as "Year2011",
CASE WHEN substr(ltrim(m_id),1,4) in ('2012') THEN SUM(cu_s_v_np) OVER( PARTITION BY ,ste_id,he_ste_id,cu_no) else 0 end) as "Year2012"
where m_id in (201205,201105)
and Ste_id in (10)
group by ste_id,he_ste_id,cu_no
LTRIM is no Teradata SQL function, it's a ODBC extension (which works in some cases in SQL Assistant), you should replace it with TRIM(m_id)
Thanks a lot, issue is solved.
Is there a way to get the Syntax list for advanced teradata functions
Here i am facing problem redgarding Teradata service control.
I am using Teradata 13.0 express edition in my pc, When try to start the teradata service control it is restarting the system. I formated the os and again i installed the Teradata but even now also u am facing the same problem. Please share the information regarding this issue.
Thanks in advance.
Hi have one more doubt is it possible to delete particular field values from a table without deleting the entire record?
My source data like as follows
Id Dept NAME
1 10 Ram
2 20 sathish
3 10 ram
4 20 sathish
Here in the dept and name columns duplicate data is existed i need to delete that duplicate data without deleting the ID of that corresponding columns. Is it possible can you please explain? please
can you please tell me how to delete the duplicate data from a table by keeping single copy of that each duplicated data record.
Other than using set table method.
Is there any alternate methods?
Thanks in advance
can anyone explain how to load a particular dept data into table by usng teradata load utilities with an example.
Here i need to skip all other dept data.
Insert all the distinct records (with all fields) into a new table and delete the old one is what i feel is easy.
yes u r right kalyan but that is not the case i need to that without using second table can any one help me
do you have a time stamp in the table?
Experience is what we get, when we don't get what we want!
First create the backup of the table as a standby -- Very important
Set the Primary index with concatenation if it is more than one field
use your dbname....
del from yourtablename
where primary index column in
sel primary index column from yourtablename
qualify row_number()over(partition by primary index order by some column) > 1
.. This will keep one record and deletes all the other. you do not need to create any seperate table for this
Hope this may serve the purpose
i have one doubt that is on which basis we need to set the nuber of sessions for a script etheir 6 or 8 or ....
in any utility can you please help
you should create new topics for new questions:
Go to the appropriate forum, at the top or bottom of the page there's a "create new forum topic"
hi frnds ,
NAME DOB AGE
K.hema 12thmarch 22
d.Hari 7thmay 35
k.kannan 2 apr 55
K.kannan 6 july 55
g.hema 12 march 31
d,hari 7 may 35
here employee id not given, such cases ve same name,same age,same dob den how to make a query for giving random number for empolyees.....
em the beginner of MS-sql any one can help.... other wise give query in oracle