Doubt with GROUP BY - MYSQL

Asked

Viewed 42 times

-1

I am making a query to my database, where it returns me several lines and groups so that I am shown only one. However, there is a column called "validated", with most values defined as "Yes". I would like if there was at least 1 "No" value, it points the "validated" column in GROUP BY to "No" as well.

SELECT assessor, 
       broker, 
       cod_cliente, 
       data_compra, 
       data_inicial, 
       id, 
       nome_cliente, 
       papel, 
       preco, 
       quantidade, 
       squad, 
       validado 
  FROM carteira_perspectiva 
 WHERE (broker = "Eric")
    GROUP BY cod_cliente
mysql

  • I don’t understand well, but if you want to do a "group by conditional" this is not possible

  • It would be if at least one of the group values is "No", it brings this values. Or else prioritize the values "No". Do you think it is not possible then?

  • It was not very clear, but from what I could imagine you would have to use the CASE statement with a condition that evaluates the client code and if there is a record "No" it replicates "No" to all the respective records of that client. That way when you group it will demonstrate "Not validated" because there was one or more "Not" records for that cod_client.

  • I’m not getting it.. Can you write what the code would look like? But that’s exactly what it would be like!

3 answers

0

Your question was unclear, I did not understand. But you can use the HAVING clause that allows you to do a type of WHERE in GROUP BY. Example

SELECT sum(valor), nome_cliente
FROM compras
GROUP BY nome_cliente
HAVING sum(valor) > 500

In this case he will make a query in the shopping table, returning the sum of all the customer’s purchases and his name, making a filter for customers who spent more than 500 reais in total. I don’t know if it’s too clear.

You can also create a PROCEDURE and do all the processing inside.

Or you can make a pre-consultation and then process it. I will use the same example that I used above returning customers who spent more than 500 real in total.

WITH compras_por_cliente as (
    SELECT sum(valor), nome_cliente
    FROM compras
    GROUP BY nome_cliente
)

SELECT *
FROM compras_por_cliente 
WHERE valor > 500

In this case it will create type a temporary table in memory called purchases_por_client with WITH and then consult this temporary table in the select below.

0

I believe a sub-select meets the need:

SELECT assessor, 
       broker, 
       cod_cliente, 
       data_compra, 
       data_inicial, 
       id, 
       nome_cliente, 
       papel, 
       preco, 
       quantidade, 
       squad, 
       (select case when count(X.validado) > 0 then 'Não' else 'Sim' end
            from carteira_perspectiva X
                where X.broker = a.broker
                  and X.cod_cliente = a.cod_cliente
                  and validado = 'Não'
       ) AS 'Validado'
  FROM carteira_perspectiva A
 WHERE (broker = 'Eric')
    GROUP BY cod_cliente

-1

SELECT assessor, 
       broker, 
       cod_cliente, 
       data_compra, 
       data_inicial, 
       id, 
       nome_cliente, 
       papel, 
       preco, 
       quantidade, 
       squad, 
       CASE 
           WHEN solicitado = 'SIM' THEN 'SIM'
           ELSE 'NÃO'
       END
  FROM carteira_perspectiva 
 WHERE (broker = "Eric")
    GROUP BY cod_cliente

Browser other questions tagged mysql

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