I have 5 tables related to sales. Three of them are like this:
Product_table_image
They are called product_a, product_b, product_c
The other tables are the time_id table which contains the reference for the date_id and the customers’ table, which contains the details of customers.
and
The 3 tables refer to the sales of different products, but the products are not important in this context, because what I need is to sum up all the values per month per customer. There are cases when one or more customers might not have been made a purchase of a certain product, which means not all customers ids will be in all products tables and that’s what I can’t figure out how to solve. It seems that my code is only able to fetch and sum when the clients have made purchases in all 3 tables.
So this is what I was able to come up with:
SELECT C.customer_id, ROUND((A.pa + B.pa + C.pc)* 1, 2) AS total,C.month_id FROM (SELECT customer.customer_id, SUM(product_a.amount) AS pa , time_id.month_id FROM customer INNER JOIN product_a on customer.customer_id = product_a.customer_id INNER JOIN time_id on product_a.date_id = time_id.date_id WHERE time_id.year_id = 558558 GROUP BY customer.customer_id, time_id.month_id) AS A CROSS JOIN (SELECT customer.customer_id, SUM(product_a.amount) AS pb , time_id.month_id FROM customer INNER JOIN product_b on customer.customer_id = product_b.customer_id INNER JOIN time_id on product_b.date_id = time_id.date_id WHERE time_id.year_id = 558558 GROUP BY customer.customer_id, time_id.month_id) AS B CROSS JOIN (SELECT customer.customer_id, SUM(product_a.amount) AS pc , time_id.month_id FROM customer INNER JOIN product_c on customer.customer_id = product_c.customer_id INNER JOIN time_id on product_c.date_id = time_id.date_id WHERE time_id.year_id = 558558 GROUP BY customer.customer_id, time_id.month_id) AS C GROUP BY C.month_id, C.customer_id ORDER BY C.month_id;
I’ve been stuck in it for a while, so any help is appreciated!
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
I have setup the tables and some sample data to make it more real.
create table month(id int, name varchar(20), primary key (id));
insert into month (id, name) values (1, 'January'),(2,'February'),(3,'March'),(4,'April');
create table year(id int, name varchar(4), primary key (id));
insert into year(id, name) values (2019, '2019'),(2020,'2020'),(2021,'2021');
create table time (id int, month_id int, year_id int, primary key (id));
alter table time add constraint fk_month FOREIGN KEY (month_id) REFERENCES month (id);
alter table time add constraint fk_year FOREIGN KEY (year_id) REFERENCES year (id);
insert into time (id, year_id, month_id) values
(1, 2019, 1),(2, 2019, 2),(3,2019,3),(4,2019,4),
(5, 2020, 1),(6, 2020, 2),(7,2020,3),(8,2020,4),
(9, 2021, 1),(10, 2021, 2),(11,2021,3),(12,2021,4);
create table customers (id int, name varchar(100), city varchar(100), country varchar(100), primary key (id));
insert into customers (id, name, city, country) values
(1, 'Google', 'San Francisco', 'US'),
(2, 'Ambev', 'São Paulo', 'BR'),
(3, 'Merck', 'Darmstadt', 'GE');
create table sales_of_product_a (id int, customer_id int, date_id int, amount decimal(10,2), primary key (id));
alter table sales_of_product_a add constraint fk_pa_time FOREIGN KEY (date_id) REFERENCES time (id);
-- only customer 1 - Google and 3 - Merck purchased product A
insert into sales_of_product_a (id, customer_id, date_id, amount) values
(1, 1, 1, 100.10),(2,1,2,200.20),(3,1,3,300.30),(4,1,4,400.40),
(5, 1, 5, 500.50),(6,1,6,600.60),(7,1,7,700.70),(8,1,8,800.80),
(9, 3, 1, 130.10),(10,3,2,230.20),(11,3,3,330.30),(12,3,4,430.40),
(13, 3, 5, 530.50),(14,3,6,630.60),(15,3,7,730.70),(16,3,8,830.80);
create table sales_of_product_b (id int, customer_id int, date_id int, amount decimal(10,2), primary key (id));
alter table sales_of_product_b add constraint fk_pb_time FOREIGN KEY (date_id) REFERENCES time (id);
-- only customer 1 - Google purchased product B
insert into sales_of_product_b (id, customer_id, date_id, amount) values
(1, 1, 1, 100.10),(2,1,2,200.20),(3,1,3,300.30),(4,1,4,400.40),
(5, 1, 5, 500.50),(6,1,6,600.60),(7,1,7,700.70),(8,1,8,800.80),
(9, 1, 9, 900.90),(10,1,10,1000.01),(11,1,11,1100.11),(12,1,12,1200.12);
create table sales_of_product_c (id int, customer_id int, date_id int, amount decimal(10,2), primary key (id));
alter table sales_of_product_c add constraint fk_pc_time FOREIGN KEY (date_id) REFERENCES time (id);
-- only customer 3 - Merck purchased product C
insert into sales_of_product_c (id, customer_id, date_id, amount) values
(1, 3, 1, 130.10),(2,3,2,230.20),(3,3,3,330.30),(4,3,4,430.40),
(5, 3, 5, 530.50),(6,3,6,630.60),(7,3,7,730.70),(8,3,8,830.80),
(9, 3, 9, 930.90),(10,3,10,1030.01),(11,3,11,1130.11),(12,3,12,1230.12);
The SQL you might be looking for would be something like.
with all_sales as (
select pa.customer_id, tt.month_id, sum(pa.amount) as amount from sales_of_product_a pa inner join time tt on (pa.date_id = tt.id) where tt.year_id = 2019 group by pa.customer_id, tt.month_id
union all
select pb.customer_id, tt.month_id, sum(pb.amount) as amount from sales_of_product_b pb inner join time tt on (pb.date_id = tt.id) where tt.year_id = 2019 group by pb.customer_id, tt.month_id
union all
select pc.customer_id, tt.month_id, sum(pc.amount) as amount from sales_of_product_c pc inner join time tt on (pc.date_id = tt.id) where tt.year_id = 2019 group by pc.customer_id, tt.month_id
),
sales_per_customer_per_month as (
-- summary of all sales of all products per customer per month
select customer_id, month_id, sum(amount) as amount from all_sales group by customer_id, month_id
),
customers_month as (
select c.id, c.name, c.city, c.country, m.id as month_id, m.name as month_name from customers c inner join month m on true
)
select c.id,c.name,c.city,c.country,c.month_id, coalesce(s.amount,0) as amount, sum(coalesce(s.amount,0)) over (partition by c.id order by c.id,c.month_id) as total
from customers_month c
left join sales_per_customer_per_month s on (s.customer_id = c.id and s.month_id = c.month_id)
order by c.id,c.month_id;
The result of query above is following.
The concepts used are linked below.
Window Functions:
https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
Common Table Expressions (CTE):
https://www.mysqltutorial.org/mysql-cte/
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0