Show date in different rows but grouped services GROUP BY

Asked

Viewed 35 times

1

Good afternoon, I am making a select where I have to show the amounts of services and name of the grouped clients, only that the different dates have to create the line with grouped services and show the different date on each line. How is my code:

select mensalidade_cliente.id_cliente,mensalidade_cliente.dt_mensal,mensalidade_cliente.status, 
  clientes.nome,
  COUNT(pedidos.id) AS QuantidadeServicos
  from mensalidade_cliente
  INNER JOIN clientes ON clientes.id = mensalidade_cliente.id_cliente
  LEFT JOIN pedidos ON pedidos.id_cliente = clientes.id group by pedidos.id_cliente;

As I have to show:

  id_cliente |  dt_mensal  | status|  QuantidadeServicos
     1       | 2020-05-01  |   1   |        2
     1       | 2020-06-01  |   2   |        2

If I take the group by it shows a single line with all the services of all customers. I need help who knows how to answer me thank.

mysql

  • Have you tried putting the mensalidade_cliente.dt_mensal in the group by?

  • @Danielmendes already yes, but then he shows the same client on different dates, but the services remain grouped in different ways.

  • This output you have shown does not match the query shown, but considering that you use LEFT OUTER JOIN maybe your GROUP BY should be per monthly charge_client.id_client since orders.id_client may be NULL.

  • @anonimo tried too, did not work :(

1 answer

2


Your query is inconsistent, you used the table mensalidade_cliente as a centre, after using a INNER JOIN seeking tuition that have a linked client, so far no problem, the error comes next, you use a LEFT JOINto search obligatorily the items of orders but that they do not need to exist in the other set and your group by still uses the table that is not mandatory with the other items 

select 
  mensalidade_cliente.id_cliente,
  mensalidade_cliente.dt_mensal,
  mensalidade_cliente.status, 
  clientes.nome,
  COUNT(pedidos.id) AS QuantidadeServicos
from mensalidade_cliente
INNER JOIN clientes ON clientes.id = mensalidade_cliente.id_cliente
LEFT JOIN pedidos ON clientes.id = pedidos.id_cliente
group by mensalidade_cliente.id_cliente, mensalidade_cliente.dt_mensal;

The order of the factors changes the product with LEFT JOIN and RIGHT JOIN

  • thank you very much, it worked right...I am still learning to use and put the language correctly, thank you very much for the help and the tips. Valew :)

Browser other questions tagged mysql

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