0

I need to select top 5 active users based on their total usage in each location.

my table structure is:

id  user  location date  usage
1    1      1     xxx   10
1    2      2     xxx   20
1    1      1     xxx   05
1    2      2     xxx   08
1    3      1     xxx   10
1    3      1     xxx   20
1    1      1     xxx   15
1    1      1     xxx   05
...
...
...

Update: I need to limit this query to return top 5 users for each location 
select `location` ,`user`,sum(`usage`) from `table`
group by `location`,`user`
order by `location` ASC,sum(`usage`) DESC

but when I put

limit 0,5

limit does not apply to group by

Update: This query limit user in group but does not sum the usage 
select `user`, `location`, `usage`
from 
(
    select `user`, `location`, `usage`,
    (@num:=if(@group = `location`, @num +1, if(@group := `location`, 1,1))) row_number 
    from `my_table` t
    CROSS JOIN (select @num:=0, @group:=null) c
    order by `location`, `usage` desc, `user`
) as x 
where x.row_number <= 2;

you can check the demo

mmta41
  • 274
  • 2
  • 13
  • Your options include doing a series of union queries, or using session variables to simulate the rank function. Look at the accepted answer in the duplicate link, and if you get stuck, come back here, edit your question, and tell us what the problem is. – Tim Biegeleisen Dec 25 '17 at 10:44
  • @TimBiegeleisen I read that link before, but it is not the answer I'm looking for – mmta41 Dec 25 '17 at 11:08
  • Well the two options in the accepted answer are basically your only options here. – Tim Biegeleisen Dec 25 '17 at 11:14
  • @TimBiegeleisen this is not a duplicated question please remove the mark – mmta41 Dec 25 '17 at 12:07
  • I disagree. I can answer this by copying the accepted query and changing the column names. – Tim Biegeleisen Dec 25 '17 at 12:08
  • @TimBiegeleisen In my case a user has more than one record in table I need to get result by sum(usage) with this query I get more than one record for same user and not sum of usage – mmta41 Dec 25 '17 at 12:22
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/161930/discussion-between-mmta41-and-tim-biegeleisen). – mmta41 Dec 25 '17 at 12:33
  • I still think that [this accepted answer](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) is your best bet. Use a union query if you only have a few locations. Otherwise, get ready to get dirty with session variables. – Tim Biegeleisen Dec 25 '17 at 12:46
  • Please create a runnable sample, for example at http://rextester.com/l/mysql_online_compiler. Include demo data a nd all tables neccessary to test it – baao Dec 25 '17 at 12:52
  • @baao sample data and sample query suggested by TimBiegeleisen included: http://rextester.com/FCLWRF11184 – mmta41 Dec 25 '17 at 13:36
  • @TimBiegeleisen please check the link: http://rextester.com/FCLWRF11184 tnx – mmta41 Dec 25 '17 at 13:36
  • I removed the duplicate mark, but I won't answer because this could take 30-45 minutes of time. Yes, I understand that you want to select top 5 using a sum, not just the column. I upvoted you. – Tim Biegeleisen Dec 25 '17 at 13:39

0 Answers0