Advanced sum aggregate fields

Question

My sql table looks like:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
create table ad(
  ad_id int,
  ad_name varchar(10)
);

create table ad_insight(
   id int,
   ad_id int,
   date date, 
   clicks int
 );
create table product(
    product_id int,
    product_name varchar(10)
);
create table product_insight(
    id int,
    product_id int, 
    sale int, 
    date date
);
create table ads_products(
    ad_id int,
    product_id int
);                              
                                    
insert into ad(ad_id, ad_name) values
  (1,'ad1'),
  (2,'ad2'),
  (3,'ad3');
 
 
insert into ad_insight(id, ad_id, date, clicks) values
    (1, 1, '2021-04-25', 1),
    (2, 1, '2021-04-24', 4),
    (3, 1, '2021-04-23', 2),
    (4, 2, '2021-04-25', 6),
    (5, 2, '2021-03-03', 7);


insert into product(product_id, product_name) values
    (1,'prod1'),
    (2,'prod2'),
    (3,'prod3'),
    (4,'prod4'),
    (5,'prod5');
insert into ads_products (ad_id, product_id) values 
    (1, 1),
    (1, 2),
    (2, 3),
    (2, 4),
    (3, 1); 
insert into product_insight(id, product_id, sale, date) values
    (1, 1, 12, '2021-04-25'),
    (2, 1, 11, '2021-04-24'),
    (3, 1, 13, '2021-04-23'),
    (4, 1, 14, '2021-04-22'),
    (5, 1, 17, '2021-04-21'),
    (6, 1, 15, '2021-04-20'),
    (7, 1, 13, '2021-04-19'),
    (8, 2, 19, '2021-04-25');

Here you have fiddle

A quick explanation of schema: I have ads:

each ad has insights, which tell us when a certain ad was active.
each ad has products(many2many - ads_products table). Each product has product_insight which tells us how many sales that product generated on a certain day.

And now I want to get the following table which will sum up clicks from ad_insight table and sum up product_sale from product_insight in 2021-04-23 to 2021-04-25 inclusive.

+----------+--------+--------------+--------------+
| ad_name  | clicks | product_sale |   products   |
+----------+--------+--------------+--------------+
| ad1      |      7 |           55 | prod1, prod2 |
| ad2      |      6 |            0 | prod3, prod4 |
| ad3      |      0 |           36 | prod1        |
+----------+--------+--------------+--------------+

What I have tried?

select ad_name, SUM(ad_insight.clicks) as clicks 
from ad 
left join ad_insight on ad.ad_id = ad_insight.ad_id 
where ad_insight.date >= '2021-04-23' and ad_insight.date <= '2021-04-25'
group by ad.ad_id;

But I do not know how to select product_sale table and products separated by a comma?

Add another join with `product_sales`. But see https://stackoverflow.com/questions/37978511/join-tables-with-sum-issue-in-mysql/37979049#37979049 for how to do SUM properly with multiple joins. — Barmar, Apr 28 '21 at 19:51
@Barmar but, how to sum up `sale` based on `GROUP_CONCAT()`? — miclofa, Apr 28 '21 at 19:51
`SUM(product_insight.sale), GROUP_CONCAT(DISTINCT product_name)` — Barmar, Apr 28 '21 at 19:52
@Barmar, I have tried to solve the problem first, but I do not know how to sum up sale based on many to many relationship, could you write how the query should look like? — miclofa, Apr 28 '21 at 19:52
So you're not even going to try? You just want someone else to do it for you? — Barmar, Apr 28 '21 at 19:53
The ad_insight conditions in the WHERE clause make the LEFT JOIN return regular INNER JOIN result. Move to ON clause if you want true LEFT JOIN result. — jarlh, Apr 28 '21 at 19:54
I gave you a link to another question that shows how to do sums with multiple joins. It wasn't helpful at all? — Barmar, Apr 28 '21 at 19:54
@Barmar, I have been trying for 3 days now, I do not know how to sum up this sale column based on many-to-many relationship. May you help me? — miclofa, Apr 28 '21 at 19:54
@Barmar Yes I have looked at linked question, but still nothing comes to my mind :( There is only mention how to sum up in subquery, but I do not know how to join these products which is many-to-many relationship. — miclofa, Apr 28 '21 at 19:56
Where is `product_sale = 36` coming from? You have no `ads_products` rows with `ad_id = 3`? — Barmar, Apr 28 '21 at 20:37
Why are `prod3` and `prod4` in the `products` list for `ad2`? They didn't have any sales during the date range. — Barmar, Apr 28 '21 at 20:40
I have updated `ads_products` table, now there is relation beetween ad_id = 3 and product_id = 3. Why are `prod3` and `prod4` in the products list for `ad2`? Because I want to list everytime list of products, which belong to certain ad, but only show sales from this time range. — miclofa, Apr 29 '21 at 05:41
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/231725/discussion-between-x123chriss-and-barmar). — miclofa, Apr 29 '21 at 07:29

Barmar · Accepted Answer · 2021-04-28T20:42:20.290

Write subqueries to get the click and sale totals for each ad in the date range. Join these with the ad table to get the ad name, and the product table to get the product names. Combine the product names using GROUP_CONCAT.

SELECT ad.ad_name, IFNULL(clicks, 0) AS clicks, IFNULL(product_sale, 0) AS product_sale, IFNULL(GROUP_CONCAT(DISTINCT p.product_name), '') AS products
FROM ad
LEFT JOIN (
    SELECT ad_id, SUM(clicks) AS clicks
    FROM ad_insight
    WHERE date BETWEEN '2021-04-23' AND '2021-04-25'
    GROUP BY ad_id
) AS ai ON ai.ad_id = ad.ad_id
LEFT JOIN (
    SELECT ad_id, SUM(sale) AS product_sale
    FROM ads_products AS ap
    LEFT JOIN product_insight AS pi ON pi.product_id = ap.product_id
    WHERE date BETWEEN '2021-04-23' AND '2021-04-25'
    GROUP BY ad_id
) AS pi ON pi.ad_id = ad.ad_id
LEFT JOIN ads_products AS ap ON ap.ad_id = ad.ad_id
LEFT JOIN product AS p ON ap.product_id = p.product_id
GROUP BY ad.ad_id

DEMO

Yeah, working on that. It's getting complicated because it also needs to join with `product_insight.date` — Barmar, Apr 28 '21 at 20:10

Advanced sum aggregate fields

1 Answers1

Linked