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

0

I have this order table, I need to count the total requests of each user and the total requests 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 this select which brings the name and total of requests of each user, however I need to bring the total of requests of each user in the current month, but I can not.

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

    
asked by anonymous 12.07.2018 / 15:40

2 answers

0

To bring the total of requests for each user in the current month, try:

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 every year as well, so there are no records of any other year made in the same month, but if you wish, just remove it.

Edited

For more information, try the following:

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: Do not forget to inform in the question what data you want as a result

    
12.07.2018 / 15:52
0

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

It would look something like this:

$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'm talking about the possibility of having a column called invoke_month. You can use your column data_creation and make an explode in the month.

    
12.07.2018 / 15:46