In Mysql group by week does not list correctly

Asked

Viewed 142 times

1

I have in Mysql the table acesso, that saves all client accesses to a system, it saves user id (id_usuario) and the day (dia) access. I am making a query to check the weekly accesses, so:

SELECT COUNT(id_usuario) AS total, dia FROM acesso
GROUP BY WEEK(dia, 1)
ORDER BY dia ASC

The problem is that it returns me strange days, some begin on Monday, others on Tuesday and often a few days repeat themselves, see an example of a return:

02/01/2019 - 08/01/2019 - 130 acessos (02/01 = quarta | 08/01 = terça)
07/01/2019 - 13/01/2019 - 165 acessos (07/01 = segunda | 13/01 = domingo)
14/01/2019 - 20/01/2019 - 163 acessos (14/01 = segunda | 20/01 = domingo)

Notice that the second result is even coming back one day.

How to make this consultation list the weeks? Because it would need a result like this (interval between Sunday to Saturday):

domingo - sábado = x acessos
domingo - sábado = x acessos
domingo - sábado = x acessos

The change of year or month does not matter. I believe that in many days would not have any access, this can be a problem to generate this way?

mysql

  • The way you grouped it is normal that they do not start and end always on the same day. Are you sure it is the WEEK function even you need? And you are sure that Mode 1 is the best for your case (attention to the "turn of the year" in this case) - It would be nice [Dit] the question and for an example of what you expected as output.

  • @Bacco then, there is another way to group other than by WEEK?

  • It depends on the result you want to get. Missing make clear in the question the desired criterion. Examples help. For example, divide the sequential number of the day by 7, group by weeks. Maybe the WEEK is good, but ai depends on the criteria of the turn of the year. If the year starts on a Tuesday, what does it do? (which will probably confuse your results with WEEK)

  • I edited! But really do not care the change of year or month, I just need to take the break from Sunday to Saturday.

  • The second function parameter WEEK(dia, 1) is informing that the weeks searched in the query always start on Monday. Do WEEK(dia, 0) so that the weeks consulted have Sunday as the starting day.

  • 1

    @Augustovasques will still have trouble grouping things from different years, and depending on where the end of the year will fall, will "separate" the turn of the year improperly. The "mode" parameter is a little more complex than just starting day setting.

  • 1

    @Bacco I would recommend to the user your answer at this time. Because I fully agree with you about the problems with the use cases of the function WEEK() and the way it addressed solves the user problem without creating side effects.

Show 2 more comments

1 answer

2


If you want to join just for the week, Monday through Friday, this is probably a better way:

SELECT COUNT(id_usuario) AS total, dia FROM acesso
GROUP BY FLOOR(TO_DAYS(dia)/7)
ORDER BY dia

If you need to change the starting day of the week, you can use a offset:

GROUP BY FLOOR((TO_DAYS(dia)+2)/7)

in place of 2 from the example you can adjust as desired.

The TO_DAYS converts the dates into calendar days since "Year Zero", and when divided by 7 we make the days of the same week return the same value.

The use of WEEK() for two reasons:

  • The treatment of the days at the turn of the year can be a little confusing, since the WEEK has modes of operation not so clear for those not used to the concept of "week of the year", common in places like the US, for example, but not in Brazil;

  • as he "reset" at the turn of the year, would group things from different years, which would not be desirable in this case. Still, would improperly separate occurrences between a year and another that would be the same week.

  • Fucnionou! But now I had another problem, maybe it’s nice to open another post. In semans that I don’t have access to, I’d have to get 0, but he just ignores it. It changes the situation a lot, right?

  • This is a classic SQL problem that has dozens of questions on the site. SQL only returns what exists, it has no concept of "empty group" (after all, only you know the concept of the group, not SQL). Probably what would be best in your case would be to treat this in the exhibition, in client language. Simply put: the problem even happens without the GROUP, imagine a simple table with 2 columns, one being the month number and the other being "sales quantity", and zero value is not released, only 1 up, and you need a SELECT of 12 months.

  • I got it, I only took it into account at the end of the project, I will study it and if I really can’t get back here. Thank you very much, I learned a lot!

  • To solve, there are N ways, and what’s best, it just depends on the case. Almost always a month-to-month loop showing the results outside of SQL is better, but if you really need to solve in pure SQL, you need to "generate" the groups in a way, but it’s kind of "ugly" in Mysql (which has no generators). Something like SELECT 1 UNION 2 UNION 3 UNION 4 ... UNION 12 THE MONTHS LEFT JOIN (your query)

  • @Bacco please really need your help, take a look PLEASE https://answall.com/questions/392025/erro-no-sql-group-by-clausand-contains-nonaggregated-column-this-is-incompat

  • 1

    @Tiago the solution is what is in this answer: https://answall.com/a/259973/70 - basically you are mixing columns with and without aggregation, and this is not accepted by default in newer versions of DB.

  • Hello @Bacco, the problem is that it was not I who made that select, was a friend who is no longer present. That select is above my knowledge. See how it worked http://sqlfiddle.com/#! 9/28638/9

  • 1

    That’s what I said and what’s in the answer, if you do not aggregate in all columns or change the DB configuration, will continue with error.

  • @Bacco if I knew how to do I would not be begging for help. Already the ONLY_FULL_GROUP_BY does not return any https://prnt.sc/o3wmipvalue

  • @Bacco Help me, please https://answall.com/questions/392025/left-join-group-by-this-is-incompatible-with-sql-mode-only-full-group-by

  • 3

    @James begging for help is not part of the scope of the site. If you have a specific new question do one that can be answered in a new question, otherwise if you already have an answer it is duplicate and can no longer be done. If you only want general and non-specific help, it is not part of the scope of the site. Making code for people who don’t know how to do is not part of the scope of the site. You need to understand at all what you do. You should learn how to program, do, and if you have a specific question post in question. What you’re doing here is wrong and could be considered abusive behavior.

Show 6 more comments

Browser other questions tagged mysql

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