1

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;
0
2

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.

0
0

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.