0

Let's say I have the following table:

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

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

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

| vendor | sum_price | sum_quantity |
|  nike  | 20        |      5       |
| adidas | 15        |      6       |

Notice how the sum of the price for 'adidas' is only 15, because they both have the same order_id, so it'll only take one. In all cases, the one to choose whenever multiple order_id's are the same doesn't matter because it will always be associated with the same price. Is it also possible to do this without a nested query?

6
  • Your question doesn't make much sense! You could do SELECT o.vendor, SUM(o.price) FROM orders o GROUP BY o.vendor ORDER BY o.vendor DESC; - result vendor sum nike 20 adidas 30 , but you don't appear to want that! Why is your desired result for adidas not 30?
    – Vérace
    Mar 26, 2021 at 19:39
  • @Vérace I only want to sum one price per order_id as I had said in the OP. So since the records with ID 3 and 4 both have order_id of 3, it would only grab one of them
    – tony
    Mar 27, 2021 at 2:28
  • But you have 3 orders, therefore you will get 3 records in your resultset? I don't understand why/how you can have 1 order (i.e. order_id 3) with two order_lines (id = 3,4) with the same vendor and price - it appears to me that we're not getting the full picture? If you have an order_id field, why have a further id field for the orders table?
    – Vérace
    Mar 27, 2021 at 9:28
  • @Vérace this is a simplified dataset. I understand the logic of the table doesn't make too much sense but i've oversimplified for the sake of this post.
    – tony
    Mar 31, 2021 at 0:50
  • 1
    What if the order with id = 4 had a different price than the one with id = 3?
    – user1822
    Mar 31, 2021 at 6:30

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.