Consultation with Count

Asked

Viewed 40 times

0

I have the following table :

Product status

SELECT id_produto_status_history, status, description, create_date, id_produto
FROM produto_status_history;

where I store product history status There is a processing that alters this status and always inserts a row in this table with different status, HOWEVER THE ITEMS THAT WERE NOT processed are only one row in this table with CART status. I wanted to do a select just to catch these guys. Pick up all products that have only the status line as CART, if you have another line do not have to come in select. IE according to the table below the select should return only ID 58 E 59

then the table looks like this :

id_produto_status_history,     status,       create_date,         id_produto
      162                      CARRINHO     2021-08-06 18:27:24       56
      164                      PAGO         2021-08-06 18:39:43       56
      166                     ESTOQUE       2021-08-06 18:39:44       56
      168                     ENVIADO       2021-08-06 18:39:54       56
      162                     CARRINHO      2021-08-06 18:27:24       57
      164                      PAGO         2021-08-06 18:39:43       57
      166                     ESTOQUE       2021-08-06 18:39:44       57
      168                     ENVIADO       2021-08-06 18:39:54       57
      162                     CARRINHO      2021-08-06 18:27:24       58
      162                     CARRINHO      2021-08-06 18:27:24       59

2 answers

2


You can do it that way:

  • Group by product (GROUP BY ID_PRODUTO), this will allow you to check each product.
  • Take only the records that the count is equal to 1, that have only 1 status (HAVING COUNT(ID_PRODUTO) = 1).

The SELECT to return the products under these conditions is thus:

select id_produto
  from historico
 group by id_produto
 having count(id_produto) = 1

Here I used the name of the table "historico", use the name of your own table. With the data of your question, will return the products 58 and 59.

You can see an example working here: http://sqlfiddle.com/

-1

From what I understand a select with Where would solve.

Select * from product_status_history Where Status = "CART"

  • this sql command compiles?

Browser other questions tagged

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