Find the best-selling item together with a certain item [duplicate]

You can use the aggregate function SUM by grouping by product only on items that have notations along with the description "salted chicken":

SELECT TOP(1) p.descricao,
              SUM(vi.qtde) AS quantidade_total
  FROM venda v
       INNER JOIN vdaitem vi ON vi.idvenda = v.idvenda
       INNER JOIN produto p ON p.codbarra = vi.codbarra
 WHERE p.descricao <> 'salgado de frango'
   AND EXISTS(SELECT 1
                FROM vdaitem vi2
                     INNER JOIN produto p2 ON p2.codbarra = vi.codbarra
               WHERE vi2.idvenda = v.idvenda
                 AND p2.descricao = 'salgado de frango')
 GROUP BY p.descricao
 ORDER BY 2 DESC

Explanation of query :

• We use JOIN to merge tables venda , vdaitem and produto . (Note that in its description there is no link column between vdaitem and venda , so I took into account that it exists and that it calls idvenda );
• We make the restriction on WHERE to prevent the product with the description "salted chicken" being considered;
• We restrict using the EXISTS sales that have the item some item with the description "salted chicken" thus ensuring that we are picking up all the records that are not chicken salty but have linking items next to it;
• We grouped using the GROUP BY clause by the descricao column of the produto table;
• We use the aggregate function SUM in the quantity of the resulting items;
• We sort by decreasing order ( DESC ) using ORDER BY by order 2 column, which in this case is the summation;
• We further restrict the result by using the expression TOP with the desired number of records.

  

TOP

     

Limits the rows returned in a query result set to a number or percentage of rows in SQL Server 2014. When TOP is used in conjunction with the ORDER BY clause, the result set is limited to the first N number of ordered rows ; otherwise, it returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT statement or affected by an INSERT, UPDATE, MERGE, or DELETE statement.

  

SUM

     

Returns the sum of all values or only the DISTINCT values in the expression. SUM can be used exclusively with numeric columns. Null values are ignored.

  

EXISTS

     

Specifies a subquery to be tested for rows.

  

GROUP BY

     

Group a set of selected rows into a set of summary rows by the values of one or more columns or expressions in SQL Server. A row is returned for each group. The aggregate functions in the SELECT clause list provide information about each group instead of individual rows.

  

ORDER BY

     

Sorts the data returned by a query in SQL Server.

Let's do it by parts.

SELECT v.idvenda,
       v.dtvenda
  FROM venda v
 INNER JOIN vdaitem vi
    ON vi.idenda = v.idvenda
 INNER JOIN produto p
    ON p.codbarra = vi.codbarra
 WHERE p.codbarra = 'CODIGO_BARRA_SALGADO_FRANGO'  -- Aqui substitui pelo valor correto

;WITH VendasContendoSalgadoFrango AS
(
    SELECT v.idvenda,
           v.dtvenda
      FROM venda v
     INNER JOIN vdaitem vi_frango
        ON vi_frango.idvenda = v.idvenda
     INNER JOIN produto p_frango
        ON p_frango.codbarra = vi.codbarra
     WHERE p_frango.codbarra = 'CODIGO_BARRA_SALGADO_FRANGO' 
), RestantesProdutos AS
(
    SELECT vFrango.idvenda, 
           vFrango.dtvenda,
           vi.codbarra,
           p.descricao,
           vi.qtde
      FROM VendasContendoSalgadoFrango vFrango
      --Ligar outra vez às tabelas vdaitem e produto, desta vez para obter os restantes produtos to cupon que contem o código do salgado de frango
     INNER JOIN vdaitem vi
        ON vi.idvenda = vFrango.idvenda
     INNER JOIN produto p
        ON p.codbarra = vi.codbarra
       AND p.codbarra <> 'CODIGO_BARRA_SALGADO_FRANGO' -- Nao queremos os detalhes do salgado de frango uma segunda vez 
)
SELECT TOP 10 codbarra,
       descricao,
       COUNT(DISTINCT idvenda),
       SUM(qtde)
  FROM RestantesProdutos
 GROUP BY codbarra, descricao
 ORDER BY 4 DESC

This will list the TOP 10 sales in terms of number of items sold. You can compare this with the total number of sales (coupons) to exclude the discrepant values (small number of sales containing high number of articles VS high number of sales containing a small number of articles)

I did something similar to a few days in order to make life easier for the user by first showing the best-selling items to the selected customer, then the best-selling items generally. I believe that it is very close to your need.

For this I used the union and put a column to sort, for your situation the query would look like this:

with rankVendas as (
select 
    codbarra,
    descricao,
    null as qtdVendida,
    0 as ordem
from produto p 
where p.codbarra='CodBarraSalgadoFrango'

union

Select top 10
    codbarra,
    descricao,
    sum(vi.qtde) as qtdVendida,
1 as ordem
from produto p
left outer join vdaitem vi on vi.codbarra = p.codbarra
where p.codbarra != 'CodBarraSalgadoFrango'
group by codbarra, descricao, ordem)

Select 
    codbarra,
    descricao
from rankVendas
order by ordem, qtdVendida desc;

  

I want to find out which item is best sold along with salted chicken.

The solution proposed in this response follows the logic defined by the author, using CTE (common table expressions) to implement it. It also considers that there is idvenda column in the vdaitem table, in order to allow the relationship between the sale and the items sold.

Here's a suggestion to get what you're asking for:

-- código #1
declare @CodFrango int;
set @CodFrango= informe o codigo de barra do salgado de frango;

with 
VendasComFrango as (
SELECT distinct idvenda
  from vdaitem
  where codbarra = @codFrango
),
VendasPorProduto as (
SELECT VI.codbarra, sum(VI.qtde) as SomaQtde
  from vdaitem as VI
       inner join VendasComFrango as VCF on VCF.idvenda = VI.idvenda
  where VI.codbarra <> @codFrango
  group by VI.codbarra
)
SELECT top (1) VPP.codbarra, 
       (SELECT descricao from produto as P where P.codbarra = VPP.codbarra) as Produto,
       somaQtde
  from VendasPorProduto as VPP
  order by VPP.SomaQtde desc;

The code is lean , using the least resources (mainly memory). For example, the product table is only associated with the end for the product description.

The variable @CodFrango must be declared in the same way as the codbarra column of the produto table.