I have the following query that creates the two columns that I need for the chart. However, the addon shows only "No data available.". I believe it is because of the "SET" part of the query. Unfortunately, MySQL doesn't allow SET to be a part of a view, and I don't know how to use procedures.
The math operation is necessary to show the total number of people (q1.1 are those who left that month)
Please tell me how to fix it.
| Code: |
SET @runtot:=0;
SELECT
CONCAT(q1.d,'-',LPAD(q1.m,2,0)) AS Periodo,
(@runtot := @runtot + q1.c - q1.l) AS `N. dipendenti`
FROM
(SELECT
YEAR(calendar.datefield) AS d,
month(calendar.datefield) AS m,
SUM(statistica_assunti_n.assunzioni) AS c,
SUM(statistica_licenziati_n.licenziamenti) AS l
FROM calendar
INNER JOIN statistica_assunti_n
ON calendar.datefield = statistica_assunti_n.data_assunzioni
INNER JOIN statistica_licenziati_n
ON calendar.datefield = statistica_licenziati_n.data_licenziamenti
WHERE YEAR(datefield) BETWEEN '2011' AND YEAR(NOW()) AND datefield <= DATE(NOW())
GROUP BY d, m
ORDER BY d, m) AS q1
|
