CODEWITHSUNDEEP
mysqlsql
miclofa
miclofa

Reputation: 49

Advanced sum aggregate fields

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:

  1. each ad has insights, which tell us when a certain ad was active.
  2. 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?

Upvotes: 0

Views: 61

Answers (1)

Barmar
Barmar

Reputation: 780974

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

Upvotes: 1

Related Questions