I was given this exercise: "ALL CUSTOMERS WHO BOUGHT MORE THAN R$ 200,00 LAST MONTH"

Asked

Viewed 193 times

-3

SELECT clientes.nome,clientes.sexo,
       SUM(pedidos.valor_pedidos) AS total_gasto,clientes.cidade

FROM clientes

INNER JOIN pedidos
  ON clientes.id_clientes = pedidos.id_clientes

WHERE sexo LIKE 'F%' 
AND valor_pedidos >= 100

GROUP BY nome;

however my query does not return the sum value, returns the unit value. Someone can help me?

php mysql sql

  • It seems to me that you are disregarding the restriction "in the last month".

2 answers

5


You forgot to group the sex. Lacked some good programming practices to facilitate reading and avoid errors, I do not like to use the conditionals at the intersection (Inner Join) as sex like 'f%' and yes in the Where, I recommend this method to avoid headaches, follow the code set:

SELECT clientes.nome,clientes.sexo,
       SUM(pedidos.valor_pedidos) AS total_gasto,clientes.cidade
   
FROM clientes

INNER JOIN pedidos
  ON clientes.id_clientes = pedidos.id_clientes

WHERE sexo LIKE 'F%' 
AND valor_pedidos >= 100

GROUP BY clientes.nome,clientes.sexo;

In this exercise that you have been through, you will need to bring the month field and group it, if you only have the date you should perform MONTH() and ignore this AND valor_orders >= 100, because you will only be able to check this result after the sum. Particularly I would use a subselect to bring the result of that consultation:

SELECT nome,sexo,mes,total_gasto

FROM(  
     
     SELECT clientes.nome,clientes.sexo,pedidos.mes
            SUM(pedidos.valor_pedidos) AS total_gasto,clientes.cidade
   
     FROM clientes

     INNER JOIN pedidos
       ON clientes.id_clientes = pedidos.id_clientes

     WHERE sexo LIKE 'F%' 

     GROUP BY clientes.nome,clientes.sexo,pedidos.mes) --SUBSELECT

WHERE total_gasto >= 200;

2

The clause HAVING may be useful for solving your problem.

Assuming there is a field data in your orders table and that "last month" is the month before the current month try:

SELECT clientes.nome, clientes.sexo, SUM(pedidos.valor_pedidos) AS total_gasto, clientes.cidade
FROM clientes
INNER JOIN pedidos ON (clientes.id_clientes = pedidos.id_clientes)
WHERE clientes.sexo LIKE 'F%'
  AND YEAR(pedidos.data) = YEAR(DATE_SUB(CURDATE(),INTERVAL 1 MONTH))
  AND MONTH(pedidos.data) = MONTH(DATE_SUB(CURDATE(),INTERVAL 1 MONTH))
GROUP BY clientes.nome, clientes.sexo, clientes.cidade 
HAVING SUM(valor_pedidos) >= 200;

Browser other questions tagged php mysql sql

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