Difficulty with Select

Asked

Viewed 40 times

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 per building and location, the maximum amount of products are 3, what I wanted was to discard the FIFO column to bring no more than 1 product result.

I tried the 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

Upshot:

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 records with FIFO.

Can you help me?

sql pl-sql
  • 1

    I couldn’t understand the need

  • Qnt_de_PNs should show 4 on all lines?

  • My sites can’t have more than 3 different products. I needed to search only the sites with more than 3 products.

2 answers

0

Try to put the distinct in 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

  • I didn’t even check the rest of the query... I considered that the syntax he posted was correct. But I can check after this

  • 1

    Rovann, Thank you! With the distinct on Count all lines got 1 in the Qnt_de_pns field. Then I made a SUM using a sub query and got the result I needed.

  • @JAN tries to put this in Sqlfiddle, I think it has how to improve.

0

You need to remove your Where condition Qnt_de_pns > 3. Thus:

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

  • I appreciate the answer, but that’s not quite it. I would like to know which places have more than 3 products, because by default we will only leave 3 for each location. And I’ll do a range search, so I wanted to filter as much as possible

Browser other questions tagged sql pl-sql

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