Question 89411 in PostgreSQL Question posted in
The official documentation can be found here.

Divide each row by the total of a subcategory – Postgresql

I have a Postgres table like:

id | value | category
0  | 10    | a
1  | 11    | b
2  | 50    | b
3  | 20    | a

and I want the following (where the expression in the last column is evaluated, of course):

id | value | category | weight
0  | 10    | a        | 10 / (10+20)
1  | 11    | b        | 11 / (11+50)
2  | 50    | b        | 50 / (11+50)
3  | 20    | a        | 20 / (10+20)

Using 2 queries you could first aggregate the per-category totals into a temporary table using select category, sum(value) from table0 group by category, and then divide the original table by the temporary table (by matching the category).

But how can I do this in Postgres in a single query?

Tags:

2

Answers


  1. 0

    You can use window function for that: fiddle

    SELECT t1."id"
     , t1."value"
     , t1."category"
     , t1."value"::decimal / SUM(t1."value") OVER w1 AS "div_1"
FROM tab1 AS t1
WINDOW w1 AS (PARTITION BY t1."category")
ORDER BY t1."id";
    id value category div_1
    0 10 a 0.33333333333333333333
    1 11 b 0.18032786885245901639
    2 50 b 0.81967213114754098361
    3 20 a 0.66666666666666666667
    Login or Signup to reply.
  2. 0

    I hope this might help you

    First Create Table named "t"

    CREATE TABLE t (
  id SERIAL PRIMARY KEY,
  value Float NOT NULL,
  category CHAR(1) NOT NULL
);

    Insert Data into table

    INSERT INTO t (id, value, category)
VALUES (0, 10, 'a'), (1, 11, 'b'), (2, 50, 'b'), (3, 20, 'a');

    Data in table

    id  value   category
0   10         a
1   11         b
2   50         b
3   20         a

    Now Query

    SELECT t.id, t.value, t.category, t.value /  ct.total_value AS weight
FROM  t
JOIN (
  SELECT category, SUM(value) AS total_value
  FROM t
  GROUP BY category
) ct ON t.category = ct.category
ORDER BY t.id;

    Result

    id  value   category    weight
0   10        a         0.333333333333333
1   11        b         0.180327868852459
2   50        b         0.819672131147541
3   20        a         0.666666666666667
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search