cocksparrer cocksparrer - 4 months ago 15
SQL Question

MySQL Left Join Count Sum with duplicate rows

I have three tables:
1. stores
2. fruity
3. vegeta

I did left join the

stores
table while the
fruity
and
vegeta
as an union.

The count and sum I need to grouping by the order_id and vendor_id but their still have duplicate rows.

Here is the tables:

mysql> SELECT * FROM stores;
+-----+-----------------+
| id | store_name |
+-----+-----------------+
| 701 | Machette Grill |
| 702 | Mateau Conserva |
+-----+-----------------+

mysql> SELECT * FROM fruity;
+------+----------+-----------+----------+-------+
| id | order_id | vendor_id | store_id | sales |
+------+----------+-----------+----------+-------+
| 1816 | 86 | 1 | 701 | 1000 |
| 1817 | 86 | 11 | 701 | 1000 |
| 1818 | 86 | 12 | 701 | 1000 |
| 1819 | 86 | 1 | 702 | 1000 |
| 1820 | 86 | 1 | 702 | 1000 |
| 1821 | 86 | 11 | 702 | 1000 |
| 1822 | 86 | 12 | 702 | 1000 |
| 1823 | 86 | 1 | 702 | 1000 |
| 1824 | 86 | 1 | 702 | 1000 |
| 1825 | 86 | 1 | 702 | 1000 |
| 1826 | 86 | 11 | 702 | 1000 |
| 1827 | 86 | 12 | 702 | 1000 |
| 1828 | 86 | 1 | 701 | 1000 |
+------+----------+-----------+----------+-------+

mysql> SELECT * FROM vegeta;
+----+----------+-----------+----------+-------+
| id | order_id | vendor_id | store_id | sales |
+----+----------+-----------+----------+-------+
| 15 | 86 | 11 | 701 | 2000 |
| 16 | 86 | 12 | 702 | 2000 |
| 17 | 86 | 11 | 701 | 2000 |
| 18 | 86 | 12 | 702 | 2000 |
| 19 | 86 | 11 | 701 | 2000 |
| 20 | 86 | 12 | 702 | 2000 |
+----+----------+-----------+----------+-------+


The code I've run below:

SELECT
s.order_id,
s.store_id,
c.store_name,
s.vendor_id,
s.fruity_count,
s.vegeta_count,
s.fruity_sum,
s.vegeta_sum
FROM stores AS c
LEFT JOIN (
SELECT
order_id AS order_id,
store_id AS store_id,
vendor_id AS vendor_id,
COUNT(sales) AS fruity_count,
0 AS vegeta_count,
SUM(sales) AS fruity_sum,
0 AS vegeta_sum
FROM fruity
WHERE order_id = 86
GROUP BY store_id,vendor_id
UNION
SELECT
order_id AS order_id,
store_id AS store_id,
vendor_id AS vendor_id,
0 AS fruity_count,
COUNT(sales) AS vegeta_count,
0 AS fruity_sum,
SUM(sales) AS vegeta_sum
FROM vegeta
WHERE order_id = 86
GROUP BY store_id,vendor_id) AS s ON s.store_id = c.id
WHERE s.order_id = 86
ORDER BY s.store_id ASC;


I just need the result like this below:

+----------+----------+-----------------+-----------+--------------+--------------+------------+------------+
| order_id | store_id | store_name | vendor_id | fruity_count | vegeta_count | fruity_sum | vegeta_sum |
+----------+----------+-----------------+-----------+--------------+--------------+------------+------------+
| 86 | 701 | Machette Grill | 1 | 2 | 0 | 2000 | 0 |
| 86 | 701 | Machette Grill | 11 | 1 | 3 | 1000 | 6000 |
| 86 | 701 | Machette Grill | 12 | 1 | 0 | 1000 | 0 |
| 86 | 702 | Mateau Conserva | 12 | 2 | 3 | 2000 | 6000 |
| 86 | 702 | Mateau Conserva | 1 | 5 | 0 | 5000 | 0 |
| 86 | 702 | Mateau Conserva | 11 | 2 | 0 | 2000 | 0 |
+----------+----------+-----------------+-----------+--------------+--------------+------------+------------+


All I want to do is moving the duplicate rows to filling the
vegeta_count
and
vegeta_sum
. Not making new row for counting them.

Please help.
Thank you.

http://sqlfiddle.com/#!9/99387/1/0

Answer

You should use group by in outer query like this :

SELECT
s.order_id,
s.store_id,
c.store_name,
s.vendor_id,
SUM(s.fruity_count) AS fruity_count,
SUM(s.vegeta_count) AS vegeta_count,
SUM(s.fruity_sum) AS fruity_sum,
SUM(s.vegeta_sum) AS vegeta_sum
FROM stores AS c
LEFT JOIN (
    SELECT
    order_id AS order_id,
    store_id AS store_id,
    vendor_id AS vendor_id,
    COUNT(sales) AS fruity_count,
    0 AS vegeta_count,
    SUM(sales) AS fruity_sum,
    0 AS vegeta_sum
    FROM fruity
    WHERE order_id = 86
    GROUP BY store_id,vendor_id
    UNION
    SELECT
    order_id AS order_id,
    store_id AS store_id,
    vendor_id AS vendor_id,
    0 AS fruity_count,
    COUNT(sales) AS vegeta_count,
    0 AS fruity_sum,
    SUM(sales) AS vegeta_sum
    FROM vegeta
    WHERE order_id = 86
    GROUP BY store_id,vendor_id) AS s ON s.store_id = c.id
WHERE s.order_id = 86
GROUP BY store_id,vendor_id
ORDER BY s.store_id ASC

SQLFiddle Demo

Comments