I have 2 tables in mysql that record the dates a certain event happens.
Table1 is the main table where I only write either session_id (in that case the date is fetched from Table2's matching record) or date. The field that I'm not filling, stays NULL.
Now, I want to count how many times that event has happened per month/year and, if possible, only do that procedure after a specific date (e.g. 01-01-2013).
Here's what I've done so far, I've created another date field (called date_final) that holds the non-NULL value per row, so I'm trying to group my data based on that field.
SELECT table1.date AS date1,
table2.session_date AS date2,
@date_final := CONCAT_WS('', table1.date, table2.session_date) AS date_final,
MONTHNAME(@date_final) AS `month`,
YEAR(@date_final) AS `year`
FROM table1
LEFT JOIN table2 ON table2.id = table1.session_id
GROUP BY YEAR(@date_final), MONTH(@date_final)
Obviously this has not worked very well, hence I'm here. MySQL returns to me only some values per month/year and that's not good. It gets even worse when I add the COUNT(*) clause. I'm just a newbie in mysql and I guess I'm getting into deep waters here :P
##Sample data for Table1##
#id session_id date#
1 1 NULL
2 NULL 2013-01-04
3 2 NULL
4 NULL 2013-01-20
5 NULL 2013-02-22
6 3 NULL
##Sample data for Table2##
#id session_date#
1 2013-01-02
2 2013-01-10
3 2013-03-02
Expected results? Something like:
2013 January 3
2013 February 2
2013 March 1
Removing the session variable since the rules of using them reliably aren't straight forward, what you seem to be looking for is a LEFT JOIN with COALESCE, something like;
SELECT
YEAR(COALESCE(table1.date, table2.session_date)) year,
MONTHNAME(COALESCE(table1.date, table2.session_date)) month,
COUNT(*) cnt
FROM table1
LEFT JOIN table2 ON table1.session_id = table2.id
GROUP BY year, month
ORDER BY year, MONTH(COALESCE(table1.date, table2.session_date))
Note that here table1.date takes precedence over table2.session_date, if you want it the other way you'll have to reverse the order in the COALESCEs.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments