Search code examples
sqlexcelstatisticsanalyticspentaho

Excel/Pentaho double group by + count

I am having some problems dealing with Excel and pentaho (I guess due to my 0 experience with these tools...). Having the following data set:

  Interaction date  interaction_name
    21/08/13    course view
    21/08/13    course view
    21/08/13    forum view forum
    21/08/13    course view
    21/08/13    course view
    21/08/13    course view
    22/08/13    forum view forum
    22/08/13    course view
    22/08/13    forum view forum
    22/08/13    user view all
    22/08/13    user view
    22/08/13    blog view
    22/08/13    user view all

I would like to represent that on the 21/08/2013 there has been 4 course views and 1 forum view, on 22/08/2013 2 forum views, 1 course view, 3 user view all... and so on.

That is (from the SQL perspective) kind of a count in a double group by. First group by date, then group by interaction and count them. The correct SQL query could also help me.

I just cant figure out how to make it. I tried with excel and with pentaho, but seems that I am missing something...

Any help would be really apreciated! Thanks

My report in pentaho so far: Already tried with aggregation count, but it counts every interaction, without making any difference among them.
enter image description here

Solution

  • As @Marcus Rickert asked, you didn't specified data source, but I will suppose that is some SQL like database because you asked for right SQL query.

    This one:

    SELECT Interaction_date, Interaction_name, count(Interaction_name) from your_table group by Interaction_date,Interaction_name order by Interaction_date asc;

    It will produce:

    Interaction_date    Interaction_name    count
---------------------------------------------
21/08/13            course view         5
21/08/13            forum view forum    1
22/08/13            forum view forum    2
22/08/13            course view         1
22/08/13            user view all       2
22/08/13            user view           1
22/08/13            blog view           1

    Now, you can put it in Pentaho Report Designer and group by (inside Report Designer) by Interaction_date.