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

Question

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

Navigation

#1 by (2 votes)

1

Good morning, I'm creating a report on the ABC curve, but I'm a beginner and I still have some difficulties.

I have a table called: ITEMS. With fields: COD_ITEM, Sales Quantity, Unit Value, Subtotal, Description

With an SQL query I was able 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 because it did not work, it brings up the fields I requested, but they are not "added" by 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 would really need to bring is a select that shows compared to the total sales how many percent represents each item

sql firebird

asked by anonymous 12.05.2017 / 15:32

1 answer

Logic to display data - Slice days How to configure the Connection String in App.config?

score 2 · Accepted Answer

Rate the code below to meet 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;