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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy