April 18, 2012 at 2:01 pm
I am trying to provide counts of different activities from one table, however few counts will come from other table and in the same query I also have to provide sum of 5 activities in one column.
I am trying something like this!
SUM(DECODE(EVENT_TYPE,2,WEB.ACTIVITY_COUNT,0)) AS BLOG
SUM(DECODE(EVENT_TYPE,5,WEB.ACTIVITY_COUNT,0)) AS DISCUSSION,
SUM(DECODE(EVENT_TYPE,33,WEB.ACTIVITY_COUNT,0)) AS Cpnw SUM(DECODE(EVENT_TYPE,34,WEB.ACTIVITY_COUNT,0)) AS Cpn_Red ,
SUM(DECODE(WEB.EVENT_TYPE,35,WEB.ACTIVITY_COUNT,0)) AS Cpn_Aprt
Most of the events are coming from one table, but in one column I have to show total of( open+view+print+register etc) and this all should come as one column as engagement _count along with other individual counts.
I am confused if I can do something like this???
SUM(DECODE(WEB.EVENT_TYPE,51,18,2,3,WEB.ACTIVITY_COUNT,0))TOTAL
I am really confused as I can do sum of one activity in decode but how do I write statement where in one decode statement it's adding up more than one activity??
And also want to ask that if some event is coming from other table can I still use the joined fields from other tables in my decode statement?
Thanks a bunch!!
April 19, 2012 at 4:35 am
Why don't you use the CASE-statement instead of decode?
sum (CASE WHEN ACTIVITY='X' OR ACTIVITY='Y' ... THEN 1 ELSE 0 END)
Also have a look an analytical functions for Oracle.
April 19, 2012 at 7:08 am
DECODE is a powerful function in Oracle. While I worked in it I saw it used many times and the Oracle developers I did talk to love it. In MS SQL Server, we have to use CASE. I find CASE easier to understand, but I could see the value of DECODE had I been given more training in Oracle while at that particular employer.
April 19, 2012 at 8:05 am
I also like case statements as I have been using Sql server all the time, but in this job they use ORACLE and part of the query was already written , I had to add few more columns where I tried something like this!
I am trying something like this!
SELECT web. OID,web. MARKETING_GROUP,
SUM(DECODE(WEB.EVENT_TYPE,5,WEB.ACTIVITY_COUNT,0)) AS DISCUSSIONCOMMENT ,
SUM(DECODE(WEB.EVENT_TYPE,6,WEB.ACTIVITY_COUNT,0)) AS DISCUSSIONSTART ,
SUM(DECODE(WEB.EVENT_TYPE,7,WEB.ACTIVITY_COUNT,0)) as DISCUSSIONVIEW,
SUM(case when o.WHEN__OPENED is not null then o.COUNT(*))as OPENCOUNT, -----In this table they don’t have seprate field named ’Actitvity_count
sum(case when c.when_clicked is not null then c.count(*))as clickcount,
SUM(case when WEB.EVENT_TYPE in(5,6,7,8)then WEB.ACTIVITY_COUNT END) +o.count(*) as Total[/highlight]
from GMMI_AIR.WEB_ACTIVITY_FCT WEB join GMMI_AIR.SILVERPOP_CLICK C
on WEB.OID_WEB_ACTIVITY_FCT = C.OID_SILVERPOP_CLICK
join GMMI_AIR.SILVERPOP_OPEN o
on web.oid_web_activity_fct = o.oid_silverpo_open
where where MARKETING_GROUP in ('PB','BTFE','EBA')
and EVENT_TYPE in (1,2,3,5,6,7,8)
AND ACTIVITY_DATE BETWEEN TO_date('04/1/2010','MM/DD/YYYY') AND TO_DATE('04/30/2010','MM/DD/YYYY')
GROUP BY web. OID,web. MARKETING_GROUP;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply