CODEWITHSUNDEEP
mysql
Pr0no
Pr0no

Reputation: 4099

Is it possible to let MySQL output in percentile groups?

I have a table with messages per user:

users
----------------
user_id messages
----------------
  725       0
   30       0
 1436       1
10454       4
    .       .
    .       .
 1507      901

The table contains 74,500 users. The table is sorted ASC for the numer of messages. I now want to count the number of tweets per percentile, so:

SELECT SUM(messages) FROM users LIMIT     0, 745;
SELECT SUM(messages) FROM users LIMIT   745, 745;
SELECT SUM(messages) FROM users LIMIT  1490, 745;
.
.
SELECT SUM(messages) FROM users LIMIT 73755, 745;

I can do this, no problem. And I could write a script to fire the queries to the database. But I was wondering, isn't it possible to let MySQL output the total number of messages per usergroup? So the output would be something like

group  users         total_messages
-----------------------------------
  1       0 - 745     451
  2     745 - 1490   5627
  .      .      .      .
  .      .      .      .
100   73755 - 74500  2654

Upvotes: 1

Views: 133

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24076

try this:

select group_id,concat(MIN(user_id) ,'-',MAX(user_id)) as users,
SUM(messages) as messages  
from
  (SELECT ceil(@rownum:= @rownum+1/745 ) AS group_id,user_id,messages
  FROM users, (SELECT @rownum:=0) r
  order by user_id)a
  group by group_id

if you want group range also to be displayed , you could do this

select group_id,concat(MIN(rownum) ,'-',MAX(rownum)) as groups,
concat(MIN(user_id) ,'-',MAX(user_id)) as users,
SUM(messages) as messages
from
  (SELECT ceil(@rownum:= @rownum+1/745 ) AS group_id,@slno:= @slno+1 
  as rownum,user_id,messages
  FROM users, (SELECT @rownum:=0)k,(select @slno:=0) r
  order by user_id)a
  group by group_id

Upvotes: 1

eggyal
eggyal

Reputation: 125925

Something like this?

SELECT grp + 1                                       AS `group`,
       CONCAT(grp*grpsize, ' - ', (grp+1)*grpsize-1) AS users,
       SUM(messages)                                 AS total_messages
FROM (
  SELECT   @i:=@i+1, @i DIV grpsize AS grp, grpsize, messages
  FROM     users, (SELECT COUNT(*) / 100 AS grpsize, @i:=0 FROM users) t
  ORDER BY messages
) t
GROUP BY grp

Upvotes: 1

Related Questions