Difficulty with Select

1

I want to make a query of 1 table that has the records:

Predio |  Local | Produto | FIFO

P01    |  WH01  |  Maçã   | 21/02/17

P01    |  WH01  |  Maçã   | 22/02/17

P01    |  WH01  |  Maçã   | 23/02/17

P01    |  WH01  |  Maçã   | 28/02/17

P01    |  WH01  |  Maçã   | 01/03/17

P01    |  WH01  |  Pera   | 10/10/17

P01    |  WH01  |  Pera   | 11/10/17

P01    |  WH01  |  Pera   | 12/10/17

P01    |  WH01  |  Pera   | 14/10/17

P01    |  WH01  |  Pera   | 15/10/17

P01    |  WH01  |  Pera   | 28/10/17

P01    |  WH01  |  Pera   | 29/10/17

P01    |  WH01  |  Manga  | 05/11/17

P01    |  WH01  |  Manga  | 08/11/17

P01    |  WH01  |  Abacaxi| 01/12/17

P01    |  WH01  |  Abacaxi| 10/12/17

Where by building and location, the maximum amount of products is 3, what I wanted was to discard the FIFO column to bring no more than 1 result of the products.

I tried Query:

SELECT DISTINCT

Predio,
Local,
Produto,
count(Produto) Qnt_de_PNs 

FROM

Controle_Prod_P_Local_E_Predio

WHERE

  Local BETWEEN ('LOCA"')
            AND ('LOCA_FINAL')   

  AND Predio BETWEEN ('Predio')
                 AND ('Predio_FINAL')
  AND Qnt_de_PNs > 3


GROUP BY Predio, Local, Produto

Result:

Predio |  Local | Produto | Qnt_de_PNs

P01    |  WH01  |  Maçã   | 5

P01    |  WH01  |  Pera   | 7

But we know that there are 4 products (Apple, Pear, Mango and Pineapple), what I need and that the Qnt_de_PNs field brings me the result of 4. This is because the count result is based on FIFO records.

Can you help me?

    
asked by anonymous 04.12.2017 / 20:42

2 answers

0

Try to put the distinct in the function:

SELECT 

Predio,
Local,
Produto,
count(DISTINCT Produto) Qnt_de_PNs 

FROM

Controle_Prod_P_Local_E_Predio

WHERE

  Local BETWEEN ('LOCA"')
            AND ('LOCA_FINAL')   

  AND Predio BETWEEN ('Predio')
                 AND ('Predio_FINAL')
  AND Qnt_de_PNs > 3


GROUP BY Predio, Local, Produto
    
04.12.2017 / 20:45
0

You need to remove from your where the condition Qnt_de_PNs > 3 . This way:

SELECT DISTINCT

Predio,
Local,
Produto,
count(Produto) Qnt_de_PNs 

FROM

Controle_Prod_P_Local_E_Predio

WHERE

  Local BETWEEN ('LOCA"')
        AND ('LOCA_FINAL')   

  AND Predio BETWEEN ('Predio')
             AND ('Predio_FINAL')


GROUP BY Predio, Local, Produto
    
04.12.2017 / 21:05