How can I SELECT rows with multiple columns where a column includes different values and GROUP BY date?

Question

I have a MySQL db company with table department (the departments in the table are sales and marketing), table campaign, table product. And two join tables department_campaign and department_product.

Each campaign has 3 promotions every day. The results of the daily promotions are stored in the table promotion_result. For instance see the rows if the campaign_id = 1:

    "created"          "campaign_id"    "promotion"   "product_id"
2020-02-06 09:42:59          1               a             281
2020-02-06 13:35:22          1               b            (null)
2020-02-06 17:55:43          1               c            (null)
2020-02-07 14:42:59          1               a             350
2020-02-07 14:42:59          1               b             350
2020-02-07 14:42:59          1               c            (null)
2020-02-08 14:42:59          1               a            (null)
2020-02-08 14:42:59          1               b            (null)
2020-02-08 14:42:59          1               c            (null)

In the column "product_id" is the product, which was sold with the promotion at the "created" time.

I would like to have one query to get a list where I can see for each day, which product with which promotion was sold by a department. If there was no product sold on a day then I would like to see to that day only one row with (null). I tried with this query in mysql:

SELECT DATE(pr.created) AS created, pr.campaign_id AS campaign, pr.promotion AS promotion, pr.product_id AS product
FROM department AS dept
          LEFT JOIN department_campaign AS dc ON dc.department_id = dept.id
          LEFT JOIN department_products AS dp ON dp.department_id = dept.id
          LEFT JOIN promotion_result AS pr ON dc.campaign_id = pr.campaign_id
          WHERE dept.id = 2 AND pr.campaign_id = 1
          GROUP BY DATE(pr.created), product_id, promotion
          ORDER BY DATE(pr.created) ASC, promotion ASC

The result:

   "created"    "campaign"   "promotion"    "product"
  2020-02-06         1            a            281
  2020-02-06         1            b           (null)
  2020-02-06         1            c           (null)
  2020-02-07         1            a            350
  2020-02-07         1            b            350
  2020-02-07         1            c           (null)
  2020-02-08         1            a           (null)
  2020-02-08         1            b           (null)
  2020-02-08         1            c           (null)

If I add AND dp.product_id = pr.product_id to the where then I get only the days where products were sold but not the day(s) whitout sales:

"created"    "campaign"  "promotion"    "product"
2020-02-06       1            a            281
2020-02-07       1            a            350
2020-02-07       1            b            350

What expected:

   "created"    "campaign"   "promotion"    "product"
  2020-02-06         1            a            281
  2020-02-07         1            a            350
  2020-02-07         1            b            350
  2020-02-08         1          (null)        (null)

In this case I could see each day with sold products per promotion and also the days without sales ("product" = (null) and "promotion" = (null))

Can anybody help me with this problem?

Strawberry · Answer 1 · 2020-02-09T13:50:51.440

0

Here is an example of a valid query:

SELECT DISTINCT DATE(pr.created) created
              , pr.campaign_id campaign
              , pr.promotion 
              , pr.product_id product
           FROM department d
           LEFT 
           JOIN department_campaign dc 
             ON dc.department_id = d.id
           LEFT 
           JOIN department_products dp 
             ON dp.department_id = dept.id
           LEFT 
           JOIN promotion_result pr 
             ON pr.campaign_id = dc.campaign_id 
            AND pr.product_id = dp.product_id  
            AND pr.campaign_id = 1
          WHERE d.id = 2 
          ORDER 
             BY DATE(pr.created) ASC, promotion ASC

edited Feb 09 '20 at 13:50

answered Feb 09 '20 at 11:29

Strawberry

33,750
13
40
57

Thanks, but I get with your query the same results listed as I already had with my query using group by. – bm82 Feb 09 '20 at 13:18
Oops - missed that – Strawberry Feb 09 '20 at 13:50

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

MySQL doesn't have recursive functionality. The given answer will work for PostgreSQL database.

Refer: https://stackoverflow.com/a/3538926/10086603 answer for generating dates in a table and right join it similiarly.

Add condition while left joining promotion_result instead in "where".

Generate series helps to fill the missing dates. (PostgreSQL)

SELECT DATE(pr.created) AS created,
       pr.campaign_id AS campaign,
       pr.promotion AS promotion,
       pr.product_id AS product
FROM department AS dept
LEFT JOIN department_campaign AS dc ON dc.department_id = dept.id
LEFT JOIN department_products AS dp ON dp.department_id = dept.id
LEFT JOIN promotion_result AS pr ON dc.campaign_id = pr.campaign_id
AND dp.product_id = pr.product_id
AND pr.campaign_id = 1
RIGHT JOIN
  (SELECT generate_series(TIMESTAMP '2020-02-01' , TIMESTAMP '2020-02-01' + interval '1 month - 1 day' , interval '1 day')::date as day) d ON pr.created = d.day
WHERE dept.id = 2
GROUP BY DATE(pr.created),
         product_id,
         promotion
ORDER BY DATE(pr.created) ASC, promotion ASC

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 09 '20 at 19:05

Sakthivel A R

575
8
24

It doesn´t work. I get only the rows where product was sold and plus a row only with (null) in all columns including created. So I can´t see on which day was no sale. – bm82 Feb 09 '20 at 19:44
@bm82 I have made some changes to above answer. Hope it helps. – Sakthivel A R Feb 10 '20 at 05:27
I get SQL error (1064): ... near 'interval '1 day'):: date as day) d ON sr.created = d.day WHERE' – bm82 Feb 10 '20 at 07:41
MySQL doesn't have recursive functionality. The given answer will work only on PostgreSQL database. Refer: https://stackoverflow.com/a/3538926/10086603 answer for generating dates in a table and right join it similiarly. @bm82 – Sakthivel A R Feb 10 '20 at 09:48
Thank you, but it doesn´t work if you have more than one result per day. I´ve found a solution and posted it here. – bm82 Feb 11 '20 at 19:41

score 0 · Accepted Answer · answered Feb 11 '20 at 19:37

I´ve found a solution using SELECT DISTINCT and UNION to get the expected result. I share it so that it can may help others, too:

SELECT DISTINCT DATE(pr.created) AS created, pr.campaign_id AS campaign, pr.promotion AS promotion, pr.product_id AS product
FROM department AS dept
LEFT JOIN department_campaign AS dc ON dc.department_id = dept.id
LEFT JOIN department_products AS dp ON dp.department_id = dept.id
LEFT JOIN promotion_result AS pr ON pr.product_id = dp.product_id
WHERE dept.id = 2 AND pr.campaign_id = 1
UNION
SELECT DISTINCT DATE(pr.created) AS created, pr.campaign_id AS campaign, NULL, pr.product_id AS product
FROM department AS dept
LEFT JOIN department_campaign AS dc ON dc.department_id = dept.id
LEFT JOIN department_products AS dp ON dp.department_id = dept.id
LEFT JOIN promotion_result AS pr ON dc.campaign_id = pr.campaign_id
WHERE dept.id = 2 AND pr.campaign_id = 1 AND pr.product_id is NULL AND DATE(pr.created) NOT IN (SELECT DISTINCT DATE(pr1.created)
FROM department AS dept1
LEFT JOIN department_campaign AS dc1 ON dc1.department_id = dept1.id
LEFT JOIN department_products AS dp1 ON dp1.department_id = dept1.id
LEFT JOIN promotion_result AS pr1 ON pr1.product_id = dp1.product_id
WHERE dept1.id = 2 AND pr1.campaign_id = 1)
ORDER BY created ASC, promotion ASC

How can I SELECT rows with multiple columns where a column includes different values and GROUP BY date?

3 Answers3