Alex Rivera | Logout

SQL random aggregate

Asked 2012-11-18T14:19:04.913
11

Say I have a simple table with 3 fields: 'place', 'user' and 'bytes'. Let's say, that under some filter, I want to group by 'place', and for each 'place', to sum all the bytes for that place, and randomly select a user for that place (uniformly from all the users that fit the 'where' filter and the relevant 'place'). If there was a "select randomly from" aggregate function, I would do:

SELECT place, SUM(bytes), SELECT_AT_RANDOM(user) WHERE .... GROUP BY place;

...but I couldn't find such an aggregate function. Am I missing something? What could be a good way to achieve this?

sql aggregate-functions
Edit
Report

1 Answer

3

I think your question is DBMS specific. If your DBMS is MySql, you can use a solution like this:

SELECT place_rand.place, SUM(place_rand.bytes), place_rand.user as random_user
FROM
  (SELECT place, bytes, user
   FROM place
   WHERE ...
   ORDER BY rand()) place_rand
GROUP BY
  place_rand.place;

The subquery orders records in random order. The outer query groups by place, sums bytes, and returns first random user, since user is not in an aggregate function and neither in the group by clause.

answered 2012-11-18T15:27:06.917

Your Answer