numediaweb numediaweb - 5 months ago 16
MySQL Question

MySQL Left join with grouping and date

As you can see on this sqlfiddle, I have this schema:

CREATE TABLE reviews
(`id` int(11) NOT NULL AUTO_INCREMENT,
`shop_id` int(11),
`order_id` char(255),
`product_id` char(32),
`review_time` int(11),
PRIMARY KEY (`id`)
)
;
INSERT INTO reviews
(`shop_id`, `order_id`, `product_id`, `review_time`)
VALUES
('10', '100', '1000', '1466190000'),
('10', '100', '1000', '1466276400'),
('10', '100', '1000', '1466462800'),
('20', '800', '8000', '1466249200')
;

CREATE TABLE tags
(`id` int(11) NOT NULL AUTO_INCREMENT,
`shop_id` int(11),
`order_id` char(255),
`product_id` char(32),
`tag_time` INT(11) NULL,
PRIMARY KEY (`id`)
)
;
INSERT INTO tags
(`shop_id`, `order_id`, `product_id`, `tag_time`)
VALUES
('10', '100', '1000', '1466449200'),
('10', '100', '1000', NULL),
('10', '100', '3000', NULL),
('20', '800', '8000', '1469449200')
;


I need to get statistics by date showing how many reviews I have per date and how many were tagged and how many were not. I'm using this query:

SELECT
DATE_FORMAT(FROM_UNIXTIME(r.`review_time`), "%d.%m.%Y") AS review_submited_on,
r.`shop_id`,
COUNT(*) as total_orders,
COUNT(*) as tagged_orders
FROM
reviews AS r
LEFT JOIN tags as t
ON r.`shop_id` = t.`shop_id` AND
r.`order_id` = t.`order_id` AND
r.`product_id` = t.`product_id`
WHERE
t.`tag_time` IS NOT NULL
GROUP BY r.`shop_id`, r.`order_id`, r.`product_id`
ORDER BY review_submited_on ASC


UPDATE
The expected result would look like this:

| review_submited_on | shop_id | total_orders | tagged_orders |
|--------------------|---------|--------------|---------------|
| 17.06.2016 | 10 | 3 | 1 |
| 18.06.2016 | 20 | 1 | 1 |


I created this sqlfiddle for demo.
Thanks for any help :)

Tin Tin
Answer

Try this, and let me know if that is something you want.

SELECT review_submited_on, shop_id, total_orders, IFNULL(tagged_orders, 0) tagged_orders
FROM
    (SELECT shop_id, COUNT(DISTINCT shop_id, order_id, product_id) total_orders, DATE_FORMAT(FROM_UNIXTIME(review_time), "%d.%m.%Y") AS review_submited_on
    FROM reviews
    GROUP BY shop_id) review_counter
    LEFT JOIN
    (SELECT shop_id, COUNT(DISTINCT shop_id, order_id, product_id) tagged_orders
    FROM tags 
    WHERE tag_time IS NOT NULL
    GROUP BY shop_id) tag_counter
USING (shop_id)

Result

| review_submited_on | shop_id | total_orders | tagged_orders |
|--------------------|---------|--------------|---------------|
|         17.06.2016 |      10 |            1 |             1 |
|         18.06.2016 |      20 |            1 |             1 |
Comments