Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Unanswered: SQL: Evaluating a log table

    I have to write a query for the following problem:

    I have a log table which is made up of these columns:
    user_id, timestamp, action_id
    which tracks the activities of a logged user on a web site.

    Every time a certain user (identified by user_id) does something (the action is identified by action_id) a new entry is created in the table.

    My table data look like this:
    user_id | timestamp | action_id
    1 2004-01-01 .... 1
    2 2004-01-01 .... 2
    2 2004-01-01 .... 1
    2 2004-01-02 .... 4
    2 2004-01-02 .... 1

    For example, "Jon Doe" is user_id = 2, a "regular log is" is action_id = 1.

    Now I've to find out how many times a specific user did a specific action,
    and when he did it the first time. So my query result should be, for example:

    user_id | first_timestamp | num_times
    2 2004-01-01 .... 2

    I was thinking of doing a:
    SELECT user_id, timestamp as first_timestamp, count(*) as num_times FROM logtable WHERE action_id=1 ORDER BY timestamp GROUP BY user_id
    But this does not work (well, it worked under mySQL....) but Postgresql complains about ungrouped attributes.

    In short: I have to group a query by a single attribute/column (user_id), get the number of rows of each grouped column (num_times) and the first occurence of "timestamp" of that group.

    Can somebody help me?

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    that it worked under mysql only shows how sloppy mysql's interpretation of GROUP BY is
    select user_id
         , min(timestamp) as first_timestamp
         , count(*)       as num_times 
      from logtable
     where action_id=1 
        by user_id | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts