Sum Decode statement in SQl
-
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 BLOGVIEW ,
SUM(DECODE(EVENT_TYPE,5,WEB.ACTIVITY_COUNT,0)) AS DISCUSSION,
SUM(DECODE(EVENT_TYPE,33,WEB.ACTIVITY_COUNT,0)) AS Cpn_View 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 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?
I am really stuck!! Appreciate all your help.
-
Your code looks like Oracle. DECODE is not part of MS SQL Servers T-SQL language.
-
This is definitely Oracle.
In SQL Server you would want to use a CASE statement.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Yes I am using Oracle, but I really like this forum and web-site as answers are always pretty simple and easy to understand:) I would appreciate if you can show me some ideas in decode statements.
Thanks.
-
nadave1123 (4/18/2012)
Yes I am using Oracle, but I really like this forum and web-site as answers are always pretty simple and easy to understand:) I would appreciate if you can show me some ideas in decode statements.Thanks.
Did you notice the name of the site? It isn't OracleCentral.com.
We are not Oracle experts around here...well there are some but they just hang out over here occasionally to see what the "other side" is doing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
OP has reposted in the Working with Oracle forum. Please post further replies here.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply