How many percent N item represents in the overall total - Query

Question

Good morning, I’m creating an ABC curve report but I’m beginner and I’m still having some difficulties.

I have a table called ITEMS. With fields: COD_ITEM, Quantityevenda, Valorunitario, Subtotal, Descricao

With an SQL query I managed to bring the total sales value:

SELECT SUM(SUBTOTAL) AS SUBTOTAL FROM ITENS
ORDER BY SUBTOTAL

And with another query that I did not understand why did not work, brings the fields I asked but they are not "added" getting several records of the same item.

SELECT COD_ITEM, QuantidadeVenda, Descricao, ValorUnitario, 
Subtotal FROM ITENS
GROUP BY COD_ITEM, QuantidadeVenda, Descricao, ValorUnitario, 
Subtotal

What you really need to bring is a select that shows how many % of each item represents compared to total sales

Answer 1

Evaluate if the code below meets your needs.

-- código #1
SELECT A.COD_ITEM, A.Subtotal,
       (A.Subtotal / B.Total * 100) as Perc
  from (SELECT COD_ITEM, sum(SUBTOTAL) as Subtotal
          from ITENS 
          group by COD_ITEM) as A
       cross join
       (SELECT sum(SUBTOTAL) as Total
          from ITENS) as B;

Or else:

-- código #2
with A as (
SELECT COD_ITEM, sum(SUBTOTAL) as Subtotal
  from ITENS 
  group by COD_ITEM
),
B as (
SELECT sum(SUBTOTAL) as Total
  from ITENS
)
SELECT A.COD_ITEM, A.Subtotal,
       (A.Subtotal / B.Total * 100) as Perc
  from A cross join B;