Mysql查询从连接中计数零，没有记录

Question

I have some spesific database tabel for shop below:

User table

+----------+
|id| name  |
+--+-------+
|1 | Mr. A |
|2 | Mr. B |
|3 | Mr. C |
+--+-------+

Category item

+--+-------+
|id| name  |
+--+-------+
|1 | Cat 1 |
|2 | Cat 2 |
|3 | Cat 3 |
+--+-------+

Item table

+---+-------------+--------+-------+
|id | category_id | title  | price |
+---+-------------+--------+-------+
|1  |1            | title 1|10     |
|2  |1            | title 2|5      |
|3  |1            | title 3|20     |
|4  |2            | title 4|10     |
|5  |2            | title 5|15     |
|6  |3            | title 6|30     |
+---+-------------+--------+-------+

Transaction table

+---+--------+--------+---------+------------+----------+
|id | user_id| item_id|buy_value|buy_at_price| date     |
+---+--------+--------+---------+------------+----------+
|1  |1       | 1      |5        |10          |2018-01-01|
|2  |1       | 3      |2        |20          |2018-01-01|
|3  |1       | 3      |1        |20          |2018-01-01|
|4  |2       | 4      |2        |10          |2018-01-01|
|5  |2       | 5      |2        |15          |2018-01-02|
+---+--------+--------+---------+------------+----------+

help me to execute a mysql query that can do a daily sales recap by category. examples of such recaps produce a table like this (if recapitulated on 2018-01-01).

Query result should be:

+-------------+--------------+--------------------------+-----------------+
| category_id | category_name| total_category_buy_value |category_income  |
+-------------+--------------+--------------------------+-----------------+
|1            | Cat 1        |8                         |110              |
|2            | Cat 2        |2                         |20               |
|3            | Cat 3        |0                         |0                |
+---+-------------+----------+--------------------------+-----------------+

but, my query not showing that Cat 3. here is my query

SELECT
    category.id AS category_id,
    category. NAME AS category_name,
    sum(`transaction`.buy_value) AS total_category_buy_value,
    sum(
        `transaction`.buy_value * `transaction`.buy_at_price
    ) AS total_income
FROM
    category
JOIN item ON item.category_id = category.id
JOIN `transaction` ON `transaction`.item_id = item.id
WHERE
    `transaction`.date LIKE '2018-01-01%'
GROUP BY
    category_id

please visit http://sqlfiddle.com/#!9/ce4b1/1/0

thank you

Answer 1

To get all the categories whether it has transactions (through items) or don't have any transaction data you can use a LEFT join to items and transaction table.

SELECT
    c.id AS category_id,
    c.name AS category_name,
    COALESCE(SUM(t.buy_value),0) AS total_category_buy_value,
    COALESCE(SUM(t.buy_value * t.buy_at_price),0) AS total_income
FROM
    category c
LEFT JOIN item i ON i.category_id = c.id
LEFT JOIN `transaction` t ON t.item_id = i.id 
                          AND t.date LIKE '2018-01-01%' 
GROUP BY c.id,c.name

In above you can also notice i have moved the date filter in ON clause from WHERE so not whole transaction table will be joined on the data that matches with this filter will be joined, If you use this clause in WHERE it will convert the outer join in an inner join

DEMO