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
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