April 18, 2012 at 12:10 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 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.
April 18, 2012 at 12:27 pm
Your code looks like Oracle. DECODE is not part of MS SQL Servers T-SQL language.
April 18, 2012 at 12:29 pm
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
April 18, 2012 at 1:27 pm
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.
April 18, 2012 at 1:38 pm
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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply