Aggregation query: group and count by multiple number of dynamically selected columns

Question

I have the following table logs:

session_id | event_type   | event_value
-----------+--------------+------------
   505     | user_profile | /admin
   505     | page_viewed  | /profile/18
   506     | user_profile | /member
   506     | page_viewed  | /home
   507     | user_profile | /admin
   507     | page_viewed  | /profile/18
   508     | user_profile | /member
   508     | page_viewed  | /profile/18

I would like to group by all possible values in event_type (user_profile and page_viewed), and count each possible combinaison.

The result should look like this:

user_profile | page_viewed | count
-------------+-------------+------
   admin     | /profile/18 |  2    # session_ids 505 and 507
   member    | /home       |  1    # session_ids 506
   member    | /profile/18 |  1    # session_ids 508

Note that the couple user_profile, page_viewed may change. It may be, for example, user_profile, user_gender, session_duration.

Counting/grouping over multiple columns could be done like that, but I have no idea how to select rows dynamically...

Is it possible to do such a query with postgresql ?

Sql fiddle

score 1 · Answer 1 · answered Nov 18 '14 at 16:22

select user_profile, page_viewed, 
       count(*) as cnt,
       '# session_ids ' ||
       STRING_AGG(session_id, ' and ' order by session_id) as sess
from (
  select max(case when event_type = 'user_profile' 
              then event_value else null end) as user_profile,
         max(case when event_type = 'page_viewed' 
              then event_value else null end) as page_viewed,
         session_id
  from logs
  group by session_id
) sub group by user_profile, page_viewed;

Some info about STRING_AGG here:
http://www.postgresonline.com/journal/archives/191-String-Aggregation-in-PostgreSQL,-SQL-Server,-and-MySQL.html

The comment `session_ids 505 and 507` was just to be explicit about what aggregation I want, I don't really want to select it. So the STRING_AGG is not really needed here. I would love to have a solution where `user_profile` and `page_viewed` is dynamic (not specified in the query). But I can do a first query to list the columns I need, and then generate this query based on what the columns are. So this is good enough. — Benjamin Crouzier, Nov 18 '14 at 16:31

score 0 · Answer 2 · answered Nov 18 '14 at 16:26

Do you mean some thing like this:(this is executed in Ms Sql Server and you may be need to change it to Postgresql)

DECLARE @selectColumns AS VARCHAR(MAX)

SET @selectColumns='user_profile,page_viewed'

EXEC('SELECT '+@selectColumns+',COUNT(*) FROM tbltest GROUP BY event_type,'+ @selectColumns)

Aggregation query: group and count by multiple number of dynamically selected columns

2 Answers2