2 Replies Latest reply: Feb 10, 2010 7:29 PM by Neaz Faiyaz RSS

    Help with grouping with the specified dimention

      I need help on counting occurrence of a filed in the table grouping by a filed that is NOT in the dimension that I have chosen.

      In SQL world the table looks like:

      Note a user can log into system any no of time in a data. My query is to plot a graph of unique visitors of the system on a daily basis BUT plot the graph on a monthly basis by summing up the daily unique visitors.

       

      Logondate|ReqID
      12/4/2009|achan
      12/3/2009|achan
      12/5/2009|alx
      12/6/2009|alx
      12/1/2009|ateo
      12/1/2009|ateo
      12/1/2009|ateo
      12/2/2009|daheller
      12/2/2009|daheller
      12/3/2009|jdoe
      12/4/2009|mssar
      12/5/2009|mssar
      12/6/2009|mssar

      My SQL query for plotting the graph is:

      select time, sum(count) tot_login from

      (select reqid, trunc(logondate, 'month') time, count(distinct logondate) count from trackuserlogin group by reqid, trunc(logondate, 'month'))

      group by time

      As you can see I have 2 levels of grouping, first to count unique days a visitor has logged into the system in a month and then SUM up this number on a monthly basis for all the users.

      I am loading logondate field rounded to month into QV.

      So my graph has dimension as login month BUT I am unable to find what to put in the expression field.

      I have tried to understand aggr function in vain.

      Kindly help or comment or point to the right forum case related to this issue I am facing.