Find the best-selling item along with a particular item

Question

To explain, I prefer an example:

I want to find out which item is the most sold along with the salty chicken.

For this, I tried the following approach and failed miserably by lack of technical knowledge:

-Fetch all coupons (sales) containing the code of the chicken salty [OK]
-Pick up all items from these coupons [OK]
-Add the individual quantity of each item that is sold. The highest value is the result.

I tried using SQL only.
The relevant structure of the tables is as follows:

Table product
codbarra | Description

Table sale
idvenda | dtvenda

Table vdaitem
idvdaitem | vdaitem | codbarra | Qtde | dtvenda

Answer 1

You can use the aggregate function SUM grouping by product only those items that have note together 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 used the JOIN to merge the tables venda, vdaitem and produto. (Note that in your description there is no link column between the vdaitem and the venda, so I took into consideration what exists and what is called idvenda);
• We performed the restriction on WHERE to prevent the product described as "salted chicken" from being considered;
• We restrict using the EXISTS sales that have the item any item with the description "salted chicken" thus ensuring that we are picking up all records that are not "chicken salty" but have link of items next to it;
• We group using the clause GROUP BY by the column descricao table produto;
• We use the aggregate function SUM in the quantity of the resulting items;
• We ordered in descending form (DESC) using ORDER BY order column 2, which in this case is the sum;
• We further restrict the result using the expression TOP with the amount of records desired.

---

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 together with the ORDER BY clause, the result set is limited to the first number N of ordered rows; otherwise, it returns the first number N 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 numerical columns. Null values are ignored.

---

EXISTS

Specifies a sub-query to be tested for lines.

---

GROUP BY

Groups 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 line is returned to each group. The aggregation functions in the list of the SELECT clause provide information on each group instead of individual lines.

---

ORDER BY

Sorts the data returned by a query in SQL Server.

Answer 2

Let’s do it in pieces.

1. Find all sales containing the salty code of chicken

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

2. Using the previous query we will select the remaining products for each of the sales:

;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 the number of items sold. You can compare this with the total number of sales (coupons) to exclude discrepant values (small number of sales containing high number of VS articles high number of sales containing a small number of items)

Answer 3

I did something similar to a few days in order to facilitate the user’s life by showing first, the best-selling items to the selected customer, then the best-selling items overall. I believe it’s very close to your need.

For this I used 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;

Answer 4

I want to find out which item is the most sold along with the salty chicken.

The solution proposed in this answer follows the logic defined by the author, using CTE (common table Expressions) to implement it. Consider also that there is a column idvenda on the table vdaitem, to enable the relationship between the sale and the items sold.

Here is suggestion to get what is requested:

-- 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 dry, using minimal resources (memory, mainly). For example, the product table is only associated with the end, to obtain the product description.

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