Find a best-selling item along with another

Asked

Viewed 69 times

0

Find a best-selling item along with another. Sql server

TABLE SALE CAMPOS = ttcupomfiscalitem

Cupomfiscal

PRODUCT TABLE FIELDS = ( tcproducts)

Produtos

I tried to make the following sqlserver query

SELECT TOP (10) c.chavecontrole   AS cupom, 
                p.descricao, 
                c.produto, 
                Sum(c.quantidade) AS quantidade 
FROM   tcprodutos p 
       INNER JOIN ttcupomfiscalitem c 
               ON p.produto = c.produto 
                  AND c.produto = '222110165' 
GROUP  BY c.chavecontrole, 
          p.descricao, 
          c.produto 
ORDER  BY quantidade DESC;

Upshot =

RESULTADO DA CONSULTA

Results appear the same product, how to make appear the other items of the same cumpom by quantity?

sql database sql-server

  • you are filtering for only one product... c.produto = '222110165'

  • @Rovannlinhalis, however wanted to know which product sells the most along with '222110165', How do I pop up the other items of the coupon that has the product = 222110165 on the same coupon. I don’t know if it’s clear. I don’t know how to make this filter.

  • 2

    Select the distinct "coupon" that has the product x , make a select of these "coupon" ( in , exists etc) counting products other than product x. Count the largest . There must be 564 other possible solutions to this recurring problem. https://answall.com/questions/226495/achar-o-item-mais-vendido-juntamente-com-determinado-item

  • This answers your question? Find the best-selling item along with a particular item

1 answer

1

SELECT TOP(1) p.descricao,
              SUM(i.quantidade) AS quantidade_total
  FROM ttcupomfiscalitem i
       INNER JOIN tcprodutos p ON p.produto = i.produto
 WHERE i.produto <> '222110165'
   AND EXISTS(SELECT 1
                FROM ttcupomfiscalitem i2
               WHERE i2.cupom = i.cupom
                 AND i2.produto = '222110165')
 GROUP BY p.descricao
 ORDER BY 2

Reference: Find the best-selling item along with a particular item

Browser other questions tagged sql database sql-server

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