problem query postgresql

Question

I have problems when I want to generate a query in posgretsql, I was trying and I do not know how to calculate the following

My table has the following data:

CREATE TABLE "abc-ventas"
AS
 SELECT *
 FROM ( VALUES
 ( '0001', 'sandía' , 2.000, 3.000 ),
 ( '0002', 'lápiz' , 3.000, 5.000 ),
 ( '0003', 'manzana', 1.000, 2.000 ),
 ( '0004', 'naranja', 2.000, 3.000 ),
 ( '0005', 'arroz' , 1.000, 5.000 )
 ) AS t(CODIGO, DESCRIPCION, COSTO, NETO);

I need to calculate the following information

PARTIC= NETO / SUM(TOTAL NETO)

CODIGO DESCRIPCION COSTO NETO PARTIC PARTIC-ACUM
0001 sandía 2.000 3.000 17%
0002 lápiz 3.000 5.000 28%
0003 manzana 1.000 2.000 11%
0004 naranja 2.000 3.000 17%
0005 arroz 1.000 5.000 28%
 -------------
 18.000 100%

Now to calculate the "partic-acum" order DESC "partic"

PARTIC-ACUM= PARTIC + PREVIOUS PARTIC

CODIGO DESCRIPCION COSTO NETO PARTIC PARTIC-ACUM CLASIF
0002 lápiz 3.000 5.000 28% 28%
0005 arroz 1.000 5.000 28% 56%
0001 sandía 2.000 3.000 17% 72%
0004 naranja 2.000 3.000 17% 89%
0003 manzana 1.000 2.000 11% 100%

Once generated the result I want to classify it in A B C

• If the value is 0 to 80: A
• If the value is 81 to 95: B
• If the value is 95 to 100: C

This is my desired output

CODIGO DESCRIPCION COSTO NETO PARTIC PARTIC-ACUM CLASIF
0002 lápiz 3.000 5.000 28% 28% A
0005 arroz 1.000 5.000 28% 56% A 
0001 sandía 2.000 3.000 17% 72% A
0004 naranja 2.000 3.000 17% 89% B
0003 manzana 1.000 2.000 11% 100% C

The query I have is the following

SELECT
 codigo,
 descripcion,
 cantidad,
 costo,
 neto,
 (SELECT sum(neto) FROM "abc-ventas") as total,
 (round(neto /(SELECT sum(neto) FROM "abc-ventas"),4)*100) AS participacion
FROM "abc-ventas"
ORDER BY participacion DESC;

Answer 1

As a first note, you should never quote table names or any identifiers in PostgreSQL. This is a bad practice.

SELECT
 *,
 CASE
 WHEN "partic-acum" > 95 THEN 'C'
 WHEN "partic-acum" > 80 THEN 'B'
 ELSE 'A'
 END AS clasif
FROM (
 SELECT
 codigo,
 descripcion,
 costo,
 neto,
 round(partic) AS partic,
 round(sum(partic) OVER (ORDER BY partic DESC, codigo)) AS "partic-acum"
 FROM (
 SELECT
 codigo,
 descripcion,
 costo,
 neto,
 neto/sum(neto) OVER ()*100 AS partic
 FROM "abc-ventas"
 ) AS t
) AS t;

Outputs

 codigo | descripcion | costo | neto | partic | partic-acum | clasif 
--------+-------------+-------+-------+--------+-------------+--------
 0002 | lápiz | 3.000 | 5.000 | 28 | 28 | A
 0005 | arroz | 1.000 | 5.000 | 28 | 56 | A
 0001 | sandía | 2.000 | 3.000 | 17 | 72 | A
 0004 | naranja | 2.000 | 3.000 | 17 | 89 | B
 0003 | manzana | 1.000 | 2.000 | 11 | 100 | C
(5 rows)

Explanation

First we generate partic with a window function. We do not round yet.

SELECT
 codigo,
 descripcion,
 costo,
 neto,
 neto/sum(neto) OVER ()*100 AS partic
FROM "abc-ventas"

This outputs

 codigo | descripcion | costo | neto | partic 
--------+-------------+-------+-------+-------------------------
 0001 | sandía | 2.000 | 3.000 | 16.66666666666666666700
 0002 | lápiz | 3.000 | 5.000 | 27.77777777777777777800
 0003 | manzana | 1.000 | 2.000 | 11.11111111111111111100
 0004 | naranja | 2.000 | 3.000 | 16.66666666666666666700
 0005 | arroz | 1.000 | 5.000 | 27.77777777777777777800
(5 rows)

Then we generate the cumulative partic.

 SELECT
 codigo,
 descripcion,
 costo,
 neto,
 round(partic) AS partic,
 round(sum(partic) OVER (ORDER BY partic DESC, codigo)) AS "partic-acum"
 FROM (
 SELECT
 codigo,
 descripcion,
 costo,
 neto,
 neto/sum(neto) OVER ()*100 AS partic
 FROM "abc-ventas"
 ) AS t;

Generating this. Notice this is where we round.

 codigo | descripcion | costo | neto | partic | partic-acum 
--------+-------------+-------+-------+--------+-------------
 0002 | lápiz | 3.000 | 5.000 | 28 | 28
 0005 | arroz | 1.000 | 5.000 | 28 | 56
 0001 | sandía | 2.000 | 3.000 | 17 | 72
 0004 | naranja | 2.000 | 3.000 | 17 | 89
 0003 | manzana | 1.000 | 2.000 | 11 | 100
(5 rows)

Then we run the whole thing in a virtual table to generate the rankings.

This may look like a more complex query, but it's all done in a single table pass -- it's the fastest solution I think you can get out of this.

Answer 2

Try this

With CTE_preselect AS (
SELECT
codigo,
descripcion,
cantidad,
costo,
neto,
(SELECT sum(neto) FROM "abc-ventas") as total,
(round(neto /(SELECT sum(neto) FROM "abc-ventas"),4)*100) AS participacion
FROM "abc-ventas")
SELECT 
codigo,
descripcion,
cantidad,
costo,
neto,
PARTICIPACION,
CASE WHEN PARTICIPACION <= 80 THEN 'A'
 WHEN PARTICAPACION > 80 AND PARTICAPACION <= 95 THEN 'B'
 WHEN PARTICAPACION > 95 THEN 'C'
 END AS 'CLASIF'
FROM preselect
ORDER BY participacion DESC;