Query mix of oracle products

Question

I made an indicator here in the company where I list product information, quantity sold, average value, etc. That in a period of time.

Now I need the following information:

Which is the product that most often appeared in the same sales note for each product on the list.

For example, whenever I see an IP camera, I see a source and 2 connectors. Therefore, in my search for the camera, the product that sells most together is the connector.

Even I have no idea how to begin this evaluation. Is there any function of Oracle that can help me?

Answer 1

SIMPLIFYING

NOTA_FISCAL_ITEM
NUMERONOTA
CODCLIENTE
CODPRODUTO

CAMERAS

SELECT NUMERONOTA
FROM   NOTA_FISCAL_ITEM
WHERE  CODPRODUTO = 'CAMERA IP'

SOLD TOGETHER

SELECT *
FROM   NOTA_FISCAL_ITEM
WHERE  CODPRODUTO <> 'CAMERA IP'
AND    NUMERONOTA IN (SELECT NUMERONOTA
                      FROM   NOTA_FISCAL_ITEM
                      WHERE  CODPRODUTO = 'CAMERA IP')

BESTSELLERS TOGETHER

SELECT CODPRODUTO , COUNT(*) QTD
FROM   NOTA_FISCAL_ITEM
WHERE  CODPRODUTO <> 'CAMERA IP'
AND    NUMERONOTA IN (SELECT NUMERONOTA
                      FROM   NOTA_FISCAL_ITEM
                      WHERE  CODPRODUTO = 'CAMERA IP')
GROUP BY CODPRODUTO
ORDER BY 2 DESC     

A simplification however, no optimization and the real problem is a little more complex.

Answer 2

Your question is very generic, but I believe that with the query below you can adapt to your use.

Basically, in my case, it lists the loads(cameras) and your tax bills(source, connectors). Note the second select within the from.

Adapt it to your use:

--- lista as cargas criadas no periodo e suas notas fiscais vinculadas
select NAME        CARGA,
       STATUS_CODE STATUS,
       TRUNC(CREATION_DATE) DATA_CRIACAO,
       notas.NotaFiscal NOTASFISCAIS
 from wsh_trips ,
      (select distinct wt.name      viagem,
                       a.trx_number NotaFiscal
            from 
               ra_customer_trx_all           a,
               wsh_trips                     wt,
               WSH_NEW_DELIVERIES            WND,
               WSH_DELIVERY_LEGS             WDL,
               WSH_TRIP_STOPS                WTS
            where trunc(a.trx_date)     between '01-JUL-12' AND '31-AGO-12'
             AND WND.DELIVERY_ID             = TO_NUMBER(A.INTERFACE_HEADER_ATTRIBUTE3(+))
             AND WND.DELIVERY_ID             = WDL.DELIVERY_ID
             AND WDL.PICK_UP_STOP_ID         = WTS.STOP_ID
             AND WTS.TRIP_ID                 = WT.TRIP_ID
             and a.status_trx <> 'VD'
            order by 1,2) Notas

 where trunc(CREATION_DATE) between '01-JUL-12' AND '31-JUL-12'       
       and name = Notas.viagem(+)