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

How to calculate subtotal to specific row number in postgres – Postgresql

I have table like follows. id is sequential number and they order ascending.

id score  name
1  10     A
2  20     B
3  30     C
4  40     D

I would like to calculate subtotal by using specific variable for example 3
in conditioning id<=3 I tried following , but a little complecated,and couldn’t achieved.

          SUM( 
                CASE 
                    WHEN main.id <= 3
                        THEN main.score
                    ELSE 0 
                    END
            ) AS subtotal
           From main

My desired result is like follows. Are there any smarter ways to achieve this?

id subtotal name 
3  60    C

Thanks

Tags:

2

Answers


  1. 0

    Try this:

    CREATE TABLE scores (
  id INTEGER,
  score INTEGER,
  name TEXT
);

INSERT INTO scores (id, score, name)
VALUES
  (1, 10, 'A'),
  (2, 20, 'B'),
  (3, 30, 'C'),
  (4, 40, 'D');

SELECT MAX(CASE WHEN id = 3 THEN id ELSE NULL END) AS id
      ,SUM(CASE WHEN id <= 3 THEN score ELSE 0 END) AS subtotal
      ,MAX(CASE WHEN id = 3 THEN name ELSE NULL END) AS name
From scores;

    enter image description here

    Login or Signup to reply.
  2. 0

    If you aggregate your data and you don’t limit this to WHERE main.id <= 3, then your approach is the typical way to achieve the result. It is called conditional aggregation. In standard SQL and PostgreSQL, too, you can get this more readable with:

    SUM(main.score) FILTER (WHERE main.id <= 3)

    or with the nulls replaced by zero as in your original expression:

    SUM(COALESCE(main.score, 0)) FILTER (WHERE main.id <= 3)

    Most DBMS don’t support the FILTER clause yet. This is why most conditional aggregation examples on the Internet use CASE expressions.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search