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

PostgreSQL query to aggregate value of a column based on combination of values of two other columns, regardless of order

I would like to perform an operation that aggregates the values of one column based on combination of values in two other columns irrespective of which column they show up in. For example, given this table:

a b 10.0 <-
b c 5.0 
c d 1.0 
b a 20.0 <-

I would like to return the following (if the aggregation I want to do is a sum):

a b 30.0 <-
b c 5.0 
c d 1.0
Tags:

2

Answers


  1. 0

    I’d suggest looking into the grouping and ordering functions, this is an example of how you can use group by and order by to achieve what you are trying to do –

    SELECT
    CASE WHEN a <= b THEN a ELSE b END AS col1,
    CASE WHEN a <= b THEN b ELSE a END AS col2,
    SUM(c) AS sum_c
FROM your_table
GROUP BY col1, col2
ORDER BY col1, col2;

    GROUP BY clause groups the rows by the unique combination of col1 and col2, ORDER BY clause sorts the result in a specified order and CASE statements to ensure that the values in columns A and B are ordered consistently so that ‘a b’ and ‘b a’ are treated as the same combination and finally, SUM function to aggregate the values in column C

    Login or Signup to reply.
  2. 0

    We can use LEAST and GREATEST in both column selection and GROUP BY clause:

    SELECT
    LEAST(col1, col2) AS col1,
    GREATEST(col1, col2) AS col2,
    SUM(col3) AS sum_c
FROM yourtable
GROUP BY 
  LEAST(col1, col2), 
  GREATEST(col1, col2)
ORDER BY col1, col2;

    Try out on this sample fiddle based on your data.

    There is no need for CASE WHEN here.

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

Back To Top
Search