SUM() each individual row with same id

Question

Given the following SQL query:

SELECT orders.id, orders.shop_id,
       products.price * line_items.quantity as total_value,
       line_items.description, line_items.id as lineitem_id, 
       line_items.order_id, products.price as price,
       line_items.product_id, line_items.quantity
from orders
JOIN line_items 
    ON line_items.order_id = orders.id
JOIN products 
    ON line_items.product_id = products.id;

produces the following result:

I'm trying to use SUM() to add together all price columns for the same order_id and have tried this new query:

SELECT orders.id, orders.shop_id, SUM(products.price),
        line_items.description, line_items.id as lineitem_id,
        line_items.order_id, products.price as price,
        line_items.product_id, line_items.quantity
from orders
JOIN line_items 
    ON line_items.order_id = orders.id
LEFT JOIN products 
    ON line_items.product_id = products.id
GROUP BY orders.id;

which does its job correctly (see below).

However, I still want to retain each row as shown in the first image AND have the total_value column summed.

In the first image, the total_value columns for the first and second row (with an order_id of 1) should be 2199.98 - is this possible?

@HeeMZa Unfortunately that produced the same result as the first image — Sean, Sep 14 '18 at 03:38
**always** identify dbms it is for (in the tags). "SQL" is not sufficient as each dbms has many syntax differences — Paul Maxwell, Sep 14 '18 at 03:45

score 2 · Accepted Answer · edited Sep 14 '18 at 04:19

Believe you need to use a subquery to achieve this:

SELECT
    orders.id
  , orders.shop_id
  , tv.total_value
  , line_items.description
  , line_items.id  AS lineitem_id
  , line_items.order_id
  , products.price AS price
  , line_items.product_id
  , line_items.quantity
FROM orders
JOIN line_items ON line_items.order_id = orders.id
JOIN products ON line_items.product_id = products.id
JOIN (
    SELECT
        line_items.order_id
      , SUM(products.price * line_items.quantity) total_value
    FROM line_items
    LEFT JOIN products ON line_items.product_id = products.id
    GROUP BY
        line_items.order_id
    ) tv ON tv.order_id = orders.id

OR

use SUM() OVER() if that is available

SELECT
    orders.id
  , orders.shop_id
  , SUM(products.price * line_items.quantity) over(partition by orders.id) total_value
  , line_items.description
  , line_items.id  AS lineitem_id
  , line_items.order_id
  , products.price AS price
  , line_items.product_id
  , line_items.quantity
FROM orders
JOIN line_items ON line_items.order_id = orders.id
JOIN products ON line_items.product_id = products.id

Thanks! Unfortunately `SUM() OVER()` is giving syntax errors but the first version with a subquery works perfectly - I just had to change `FROM line_items.order_id` to `FROM line_items` in the `JOIN ( ... )`. — Sean, Sep 14 '18 at 04:15
good catch, sorry I overlooked that. Please keep in mind that when asking SQL related questions we do need to know which dbms brand — Paul Maxwell, Sep 14 '18 at 04:20

score 1 · Answer 2 · answered Sep 14 '18 at 03:41

I'm not sure which DBMS or database software you are using, but you might consider using CTE, or Common Table Expressions, here. The method below, WITH...AS does not work with mySQL but will in Oracle or SQL Server, or MariaDB, or something else. If you are using mySQL, we can rewrite it to work properly.

Ideally, what you are going to do is create two temporary tables.

The first temporary table will contain the results of your first query
Your second temporary table will contain the results of your second query
Then, what we want to do is join those tables, so that for everything where the order_id is 1 or 2, it returns the total value for that order [that we calculated in the second query]
Ultimately, you just want to select the proper data

Here is an example of SQL code that should work assuming you aren't on mySQL:

WITH firstQuery AS
(
    SELECT orders.id, orders.shop_id,
       products.price * line_items.quantity as total_value,
       line_items.description, line_items.id as lineitem_id, 
       line_items.order_id, products.price as price,
       line_items.product_id, line_items.quantity
from orders
JOIN line_items 
    ON line_items.order_id = orders.id
JOIN products 
    ON line_items.product_id = products.id;
),

secondQuery AS
(
    SELECT orders.id, orders.shop_id, SUM(products.price) as "total_price",
        line_items.description, line_items.id as lineitem_id,
        line_items.order_id, products.price as price,
        line_items.product_id, line_items.quantity
from orders
JOIN line_items 
    ON line_items.order_id = orders.id
LEFT JOIN products 
    ON line_items.product_id = products.id
GROUP BY orders.id;
)

SELECT
    firstQuery.*,
    total_price
FROM
    firstQuery LEFT JOIN secondQuery ON firstQuery.order_id = secondQuery.order_id

I suggest reading up on CTE here: Which are more performant, CTE or temporary tables?

It is extremely powerful and certainly gives you incredible abilities when it comes to data and analytics.

Let me know if that works for you, if not, we can work through it and employ CTE in a different way :)

UPDATE - saw you mentioned you use SQLite. This should work there. Here is a good readup on what we are achieving, using subqueries in an easy format: https://www.sqlite.org/lang_with.html

Thanks for the detailed answer - unfortunately the `WITH` clause was throwing a handful of syntax errors. I'll try exploring why in the near future! — Sean, Sep 14 '18 at 04:20
Sorry to hear, @Sean. If you need help with implementing a different type of subquery, let me know and I'd be happy to assist. — artemis, Sep 14 '18 at 04:23

SUM() each individual row with same id

2 Answers2