On WEB.OID_WEB_ACTIVITY_FCT = C. SUM(case when WEB.EVENT_TYPE in(5,6,7,8)then WEB.ACTIVITY_COUNT END) +o.count(*) as Totalįrom GMMI_AIR.WEB_ACTIVITY_FCT WEB join GMMI_AIR.SILVERPOP_CLICK C Sum(case when c.when_clicked is not null then c.count(*))as clickcount, 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(DECODE(WEB.EVENT_TYPE,7,WEB.ACTIVITY_COUNT,0)) as DISCUSSIONVIEW, SUM(DECODE(WEB.EVENT_TYPE,6,WEB.ACTIVITY_COUNT,0)) AS DISCUSSIONSTART , SUM(DECODE(WEB.EVENT_TYPE,5,WEB.ACTIVITY_COUNT,0)) AS DISCUSSIONCOMMENT , 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 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?Īnd 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? I am confused if I can do something like this? See Also: Examples This example decodes the value warehouseid. The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255. The arguments can be any of the numeric types ( NUMBER, BINARYFLOAT, or BINARYDOUBLE) or. If default is omitted, then Oracle returns null. If no match is found, then Oracle returns default. If expr is equal to a search, then Oracle Database returns the corresponding result. If expr is null, then Oracle returns the result of the first search that is also null. DECODE compares expr to each search value one by one. 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. In a DECODE function, Oracle considers two nulls to be equivalent. SUM(DECODE(WEB.EVENT_TYPE,35,WEB.ACTIVITY_COUNT,0)) AS Cpn_Aprt ![]() ![]() 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(EVENT_TYPE,5,WEB.ACTIVITY_COUNT,0)) AS DISCUSSION, SUM(DECODE(EVENT_TYPE,2,WEB.ACTIVITY_COUNT,0)) AS BLOG 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |