SQL queries: how to add "sum" along with related data
I have a many-to-many relationship and I would like to select the sum of a specific field along with all the required data. I'll just explain my case with code.
First, here are the relevant table structures:
CREATE TABLE `products` (
`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT,
`name` text,
`description` text,
`price` float,
`picture` varchar(255)
);
CREATE TABLE `orders_products` (
`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT,
`quantity` integer,
`status` boolean,
`product_id` integer,
`order_id` integer,
FOREIGN KEY(`product_id`) REFERENCES "products" ( "id" ),
FOREIGN KEY(`order_id`) REFERENCES "orders" ( "id" )
);
CREATE TABLE `orders` (
`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT,
`created_at` datetime,
`updated_at` datetime,
`user_id` integer,
FOREIGN KEY(`user_id`) REFERENCES "users" ( "id" )
);
Basically what I have done now is:
select
"products".name,
"products".price,
"orders_products"."order_id",
"orders_products"."product_id"
from "products"
inner join "orders_products" on "orders_products"."product_id" = "products"."id"
where "orders_products"."order_id" in (1, 2)
This leads to the following result:
name price order_id product_id
"coca cola" "2.35" "1" "1"
"snickers" "1.25" "1" "2"
"snickers" "1.25" "2" "2"
"popcorn" "1.19" "2" "3"
However, if I want to "sum" the price and order it based on the order_id:
select
sum ("products".price) as _sum,
"products".name,
"products".price,
"orders_products"."order_id",
"orders_products"."product_id"
from "products"
inner join "orders_products" on "orders_products"."product_id" = "products"."id"
where "orders_products"."order_id" in (1, 2)
group by "orders_products"."order_id"
I get the following result:
_sum name price order_id product_id
"3.6" "snickers" "1.25" "1" "2"
"2.44" "popcorn" "1.19" "2" "3"
What I really like:
name price order_id product_id _sum
"coca cola" "2.35" "1" "1" "3.6"
"snickers" "1.25" "1" "2" "3.6"
"snickers" "1.25" "2" "2" "2.44"
"popcorn" "1.19" "2" "3" "2.44"
Is it possible?
source to share
What you can do is design sums
each order id
as a derived table and then join the original query to the derived table, which will allow you to show sum
as a column for each the order:
select
p.name,
p.price,
op.order_id,
op.product_id,
Totals.theSum
from products p
inner join orders_products op on op.product_id = p.id
inner join
(select sum(p.price) as theSum,
op.order_id
from products p
inner join orders_products op on op.product_id = p.id
where op.order_id in (1, 2)
group by op.order_id) AS Totals
on Totals.order_id = op.order_id;
The filtering has already been done on the internal view, so there is no need to repeat it.
source to share