Sum of column per distinct ID and group by different column

user_7823702 0
Viewed 87

Let's say I have the following table:

create table orders (id int, order_id int, vendor varchar, price int);

INSERT INTO orders values 
(1, 1, 'nike', 10),
(2, 2, 'nike', 10),
(3, 3, 'adidas', 15),
(4, 3, 'adidas', 15);

I want to get the sum of the price per distinct order_id and grouped by vendor. So the answer here would be:

| vendor | sum |
|  nike  | 20  |
| adidas | 15  |

The reason why adidas is 15 is because we only grab one of the prices since the other adidas record has the same order_id. Is it also possible to do this without a nested query?

postgresql sql
3 Answers

In Postgres, I would use distinct on with group by:

select vendor, sum(price)
from (select distinct on (order_id, vendor) o.*
      from orders o
      order by order_id, vendor
     ) o
group by vendor;

Note: If prices are different within a given order, this chooses an arbitrary price.

I suggest this because distinct on is usually the fastest method for reducing data in this fashion.

user_11440350

You can use a self join with case:

select o.vendor, 
    case when count(*) = count(distinct o1.order_id) 
    then sum(o1.price) 
    else sum(distinct o1.price) 
    end
from orders o 
join orders o1 on o.vendor = o1.vendor and o.order_id = o1.order_id
group by o.vendor;

Output:

vendor sum
nike 20
adidas 15

See demo.

user_73267380

Querying in two parts is pretty straightforward. First getting the distinct orders, then doing the sum:

WITH distinct_orders AS (
    SELECT vendor, order_id, min(price) as price
    FROM orders
    GROUP BY vendor, order_id
)
SELECT vendor, sum(price)
FROM distinct_orders
GROUP BY vendor;

If it's really important to you to do this with just one select, you can use a self-join, but note that this isn't actually a better solution:

SELECT o.vendor, sum(o.price)
FROM orders o
LEFT JOIN orders o1 
  ON (o.vendor = o1.vendor AND o.order_id = o1.order_id and o.ctid < o1.ctid)
WHERE o1.id IS NULL
GROUP BY o.vendor;
user_68978070
Related