Limit number of Group By occurrences

Asked

Viewed 792 times

3

I have to search in a table data of an advertiser, I need to group by the advertiser according to the most viewed, separated by the date.

I wanted to limit the group by, has as?

Example table:

id       visualizacoes       data
485            19            20/05/2105
485            68            11/03/2105
121            45            05/04/2105
485            20            20/05/2105
370            37            26/05/2105
225            81            20/05/2105
485            75            07/03/2105
485            11            20/05/2105
485            46            20/05/2105
370            23            16/04/2105
370            15            09/05/2105
121            7             21/04/2105
680            10            20/05/2105
370            68            26/05/2105
370            92            26/05/2105

I want to group by id_anunciante and add up the views with the same date, but wanted to limit up to 3 registrations per advertiser.

Initial query:

SELECT *, SUM(visualizacoes) AS total_visu 
FROM anunciante 
GROUP BY id,data
mysql sql

  • Post the query you already developed to fetch this data

  • this query brings the right result, for example id 485 has 4 records of the day 20/05/2015 want only 3. So of 3 in 3 ids.

  • I think it can be solved with a simple query. But it’s not entirely clear to me what you want. You have shown how your table is, if in the same way you show how the SELECT result would look I can offer an answer.

  • Okay Caffé, in my result, brings everything grouped by date and adding the views ,right, so assuming that the table brought 20 results, of these 5 are from id 485, then in this case I want it to be only 3 records, understood?

  • @Rafael Yes, I understand. And we already have an answer accepted :-)

2 answers

3


By default Mysql does not do this, but we can improvise.

Assuming your original query is this

SELECT id, data, SUM(visualizacoes)
FROM tabela
GROUP BY id, data 
ORDER BY id, data DESC;

Place your query in a subquery, an extra column will be added that will count the repetition of certain id:

SELECT id, data, totalVisualizacoes,
@currcount := IF(@currvalue = id, @currcount + 1, 1) AS rank,
@currvalue := id FROM (
    SELECT id, data, SUM(visualizacoes) AS totalVisualizacoes
    FROM tabela
    GROUP BY id, data 
    ORDER BY id, data DESC;
) AS totais;

Finally, make the final query that will filter the number of occurrences:

SELECT id, data, totalVisualizacoes FROM (
    SELECT id, data, totalVisualizacoes,
    @currcount := IF(@currvalue = id, @currcount + 1, 1) AS rank,
    @currvalue := id FROM (
        SELECT id, data, SUM(visualizacoes) AS totalVisualizacoes
        FROM tabela
        GROUP BY id, data
        ORDER BY id, data DESC
    ) AS totaisPorData
) AS totalLimitado
WHERE rank <= 3;

Example in slqfiddle.

Reference

  • 1

    interesting your answer, very good, but I tested here and I appears 4 occurrences of the example of id 485, even if at the end has the "rank<=3"

  • True, missed the ORDER BY... corrected

  • it still didn’t work out, you managed to test?

  • http://sqlfiddle.com/#! 9/bc3ef/3 - Works exactly with the information you posted. If the goal is just to count, make sure only the required columns are in select and group by.

  • in your example worked even, I tested in sqlfiddle, but on my phpMyAdmin server did not give, showed more results, is it the mysql version? mine is 5.5.42. On the sqlfiddle site is 5.5 or 5.6, it has something to do with?

  • No, the problem should be the amount of columns you are using. Try not to use the * and if you need more columns, insert them into ORDER BY.

  • 1

    Thanks, I put the same amount of column worked, ball show!

Show 2 more comments

0

I had a very similar problem and I couldn’t solve it through reply from the gmsantos, however I found another very similar answer by the user Salman A (link to the reply), it was then that I was able to understand how it works to count occurrences of a value in a specific field, it worked 100% for me!
Pay attention to the first line where you have:

SET @currcount = NULL, @currvalue = NULL;

this line must always be present for the occurrence count to work. (If you are editing sql in phpmyadmin this line will disappear if you run the query and click edit again)

Note: My problem was selecting the last 20 offers of each company in the bank

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.