gugoan gugoan - 3 months ago 12
MySQL Question

Recursive Query for only the parent categories - By period

In SQL below , I need to display only the records of the current month and year.

SELECT y.desc_category as segment, sum( x.value) as total FROM (
SELECT category.id_category, category.desc_category, category.parent_id , c.value AS value
FROM category
INNER JOIN cashbook AS c ON category.id_category = c.category_id )AS x
INNER JOIN category AS y ON x.parent_id = y.id_category
INNER JOIN user AS u ON y.user_id = u.id
WHERE u.id = 3
GROUP BY y.desc_category


I asked this post Mysql - Recursive Query for only the parent categories but there was no response and the question was another. Can anybody help me

My schema:

I have the following table
category
:

id | desc_cat | parent_id
19 | Personal | (null)
20 | Credit Card | 19
21 | Academy | 19
22 | Home | (null)
23 | Water | 22
24 | Energy | 22
25 | Rent | 22


And I have a table containing entries with name
cashbook
:

id | value | category_id | date
177 | 480.55 | 20 | 2016-05-01
178 | 100.00 | 24 | 2016-05-04
179 | 580.00 | 25 | 2016-05-05
180 | 80.00 | 21 | 2016-05-09
181 | 28.00 | 23 | 2016-05-11

Answer

You should select year and month for date .. and group by

SELECT x.`year`, x.`month`, y.desc_category as segment, sum( x.value) as total FROM (
SELECT category.id_category, category.desc_category, category.parent_id , c.value AS value, 
       year(c.`date`) as `year` , month(c.`date`) AS `month`
FROM category
INNER JOIN cashbook AS c ON category.id_category = c.category_id ) AS x 
INNER JOIN category AS y ON x.parent_id = y.id_category
INNER JOIN user AS u ON y.user_id = u.id
WHERE u.id = 3
GROUP BY y.desc_category, x.`year`, x.`month`

and a easy way for filtering by actual year and month is use having and now

SELECT x.`year`, x.`month`, y.desc_category as segment, sum( x.value) as total FROM (
SELECT category.id_category, category.desc_category, category.parent_id , c.value AS value, 
       year(c.`date`) as `year` , month(c.`date`) AS `month`
FROM category
INNER JOIN cashbook AS c ON category.id_category = c.category_id ) AS x 
INNER JOIN category AS y ON x.parent_id = y.id_category
INNER JOIN user AS u ON y.user_id = u.id
WHERE u.id = 3
GROUP BY y.desc_category, x.`year`, x.`month`
having x.`year` = year(now())  and x.`month` = month(now())