CODEWITHSUNDEEP
sqlsqlite
Sean
Sean

Reputation: 517

SUM() each individual row with same id

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:

sql query 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).

sql query result with SUM

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?

Upvotes: 0

Views: 73

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35563

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

Upvotes: 2

artemis
artemis

Reputation: 7241

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.

  1. The first temporary table will contain the results of your first query
  2. Your second temporary table will contain the results of your second query
  3. 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]
  4. 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

Upvotes: 1

Related Questions