I try to count elements between dates and operate between rows agnostically. As an example, suppose the month starts from day 2 until day 2 of next month. The table has fields {timestamp, status} and the status field has 3 different types. I do not know how to make it count between dates. Also, I would like to have the percentage of statuses.

Thank you for the help sincerely.

  • Please consider reading these suggestions. – mustaccio Apr 7 at 15:36
  • Nice. And the "between date" question? Pivoting sounds a solution, but I would like to pivot a query result, and not a table. – Bruno Henrique Peixoto Apr 7 at 15:40
  • Hi, and welcome to the forum! A query result is also a table in its own right! Please provide your tables (as DDL - text), some sample data and your desired result - use dbfiddle.uk - pay attention to versions. Also, put anything on the fiddle here also! Finally, with any database question, always include your server version - any answer may vary tremendously depending on that! – Vérace Apr 7 at 16:15
WHERE timestamp >= '2021-04-02'
  AND timestamp  < '2021-04-02' + INTERVAL 1 MONTH
  AND status = "done"

(If you need to discuss further, please provide SHOW CREATE TABLE and more details about the desired query.)

And this index may help with performance:

INDEX(status, timestamp)

Not the answer you're looking for? Browse other questions tagged or ask your own question.