Thread: Most Occurring Value

There is probably a really simple solution for this problem, but for the
life of me I can't see to think of it.  I have three tables

--contains u/p for all users in the site
TABLE users (user_id INT primary key, username VARCHAR(50), password TEXT)
--list of all possible events (login, logout, timeout)
TABLE events (event_id INT primary key, event VARCHAR(255))
--logs the activity of all users logging in/out, etc
TABLE log (log_id INT primary key, user_id INT REFERENCES users,
event_id INT REFERENCES event);

How would I query to find out which user has the most activity?
SELECT user_id, COUNT(event_id)
FROM log
GROUP BY (user_id)
HAVNG COUNT(event_id) = ???

Any and all help is appreciated. Thank you.

Mike Ginsburg
mginsburg@collaborativefusion.com

Mike Ginsburg wrote:
> There is probably a really simple solution for this problem, but
> for the life of me I can't see to think of it.  I have three tables
>
>
> --contains u/p for all users in the site TABLE users (user_id INT
> primary key, username VARCHAR(50), password TEXT) --list of all
> possible events (login, logout, timeout) TABLE events (event_id INT
> primary key, event VARCHAR(255)) --logs the activity of all users
> logging in/out, etc TABLE log (log_id INT primary key, user_id INT
> REFERENCES users, event_id INT REFERENCES event);
>
> How would I query to find out which user has the most activity?
> SELECT user_id, COUNT(event_id) FROM log GROUP BY (user_id) HAVNG
> COUNT(event_id) = ???
>
> Any and all help is appreciated. Thank you.

I'd say...

SELECT user_id, count(event_id) AS event_count FROM log GROUP BY
user_id ORDER BY event_count DESC LIMIT 1;

Or something to that effect.

Colin

Mike Ginsburg <mginsburg@collaborativefusion.com> writes:
> There is probably a really simple solution for this problem, but for
> the life of me I can't see to think of it.  I have three tables
>
> --contains u/p for all users in the site
> TABLE users (user_id INT primary key, username VARCHAR(50), password TEXT)
> --list of all possible events (login, logout, timeout)
> TABLE events (event_id INT primary key, event VARCHAR(255))
> --logs the activity of all users logging in/out, etc
> TABLE log (log_id INT primary key, user_id INT REFERENCES users,
> event_id INT REFERENCES event);
>
> How would I query to find out which user has the most activity?
> SELECT user_id, COUNT(event_id)
> FROM log
> GROUP BY (user_id)
> HAVNG COUNT(event_id) = ???

SELECT user_id, max(count(event_id))
  FROM log
 GROUP BY user_id;

or

SELECT user_id, count(event_id)
  FROM log
 GROUP BY user_id
 ORDER BY count(event_id) DESC
 LIMIT 1;


Regards.

P.S. It'd be better if you can send such questions to pgsql-sql mailing
     list.

Volkan YAZICI escreveu:
> Mike Ginsburg <mginsburg@collaborativefusion.com> writes:
>> There is probably a really simple solution for this problem, but for
>> the life of me I can't see to think of it.  I have three tables
>>
>> --contains u/p for all users in the site
>> TABLE users (user_id INT primary key, username VARCHAR(50), password TEXT)
>> --list of all possible events (login, logout, timeout)
>> TABLE events (event_id INT primary key, event VARCHAR(255))
>> --logs the activity of all users logging in/out, etc
>> TABLE log (log_id INT primary key, user_id INT REFERENCES users,
>> event_id INT REFERENCES event);
>>
>> How would I query to find out which user has the most activity?
>> SELECT user_id, COUNT(event_id)
>> FROM log
>> GROUP BY (user_id)
>> HAVNG COUNT(event_id) = ???
>
> SELECT user_id, max(count(event_id))

max(count() is invalid.
aggregate function calls may not be nested


>   FROM log
>  GROUP BY user_id;
>
> or
>
> SELECT user_id, count(event_id)
>   FROM log
>  GROUP BY user_id
>  ORDER BY count(event_id) DESC
>  LIMIT 1;

If more than 1 user has the most activity only one is listed.

Try:

SELECT user_id, COUNT(event_id)
   FROM log
   GROUP BY (user_id)
   HAVING COUNT(event_id) = (SELECT max(l.ct) FROM
         (SELECT count(event_id) AS ct FROM log GROUP BY user_id) AS l)
   ORDER BY user_id;

Osvaldo