Calculate total orders and total orders for the current month with Mysql

Question

I have this order table, I need to count the total order of each user and the total order of each user in the current month

| id | id_user | name | data_criacao |
| 1  | 10      |Pedro | 2018-05-01   |
| 2  | 10      |Pedro | 2018-05-03   |
| 3  | 15      |Joao  | 2018-05-04   |
| 4  | 10      |Pedro | 2018-05-10   |
| 5  | 18      |Maria | 2018-05-07   |

I made that select that brings the name and total order of each user, but I need to bring the total order of each user in the current month, but I’m not getting.

select name, count(*) as total from pedidos group by id_user;

Answer 1

Hi @Daywison Ferreira Leal, you need to do a BETWEEN in the current month.

it would be something like that:

$mes_atual = date('m');

select name, count(*) as total from pedidos WHERE mes_vigente BETWEEN $mes_atual AND $mes_atual group by id_user;

In this example, I talk about the possibility of you having a column called mes_vigente. You can use your data_creation column and make a explode in the month.

Answer 2

To bring the total order of each user in the current month, try this way:

select name
      ,count(*) as total 
  from pedidos  
 where Month(data_criacao) = Month(CURRENT_DATE())
   and Year(data_criacao)  = Year(CURRENT_DATE())
 group by id_user;

I added the filter per year also, not to appear records of some other year made in the same month, but if you wish just remove.

Edited

If you want more information, try it this way:

select name
      ,count(*) as total_geral
      ,count(CASE WHEN Month(data_criacao) = Month(CURRENT_DATE()) and Year(data_criacao) = Year(CURRENT_DATE()) THEN 1 END) as total_usuario     
  from pedidos
 group by id_user;

Note: Don’t forget to inform on the question what data you want as a result