2 series from db

Hi,

The first serie are year

I would want to add 2 series from a table where a column named session_type has 2 possible values:
"Distance learning" or "Classroom"

With 2 queries

SELECT YEAR(r.date_event) AS y, count(r.id) AS n
FROM #__admincrm_programs as p
LEFT JOIN #__admincrm_versions as v ON(v.programs_id=p.id)
LEFT JOIN #__admincrm_sessions as s ON(s.versions_id=v.id)
LEFT JOIN #__admincrm_enrollments AS e ON(e.sessions_id=s.id)
LEFT JOIN #__admincrm_results AS r ON(r.enrollments_id=e.id)
WHERE p.id IN(2,7,16,17) AND r.score>0 AND s.session_format='Distance learning' AND r.date_event!='0000-00-00'
GROUP BY YEAR(r.date_event);

SELECT YEAR(r.date_event) AS y , count(r.id) AS n
FROM #__admincrm_programs as p
LEFT JOIN #__admincrm_versions as v ON(v.programs_id=p.id)
LEFT JOIN #__admincrm_sessions as s ON(s.versions_id=v.id)
LEFT JOIN #__admincrm_enrollments AS e ON(e.sessions_id=s.id)
LEFT JOIN #__admincrm_results AS r ON(r.enrollments_id=e.id)
WHERE p.id IN(2,7,16,17) AND r.score>0 AND s.session_format='Classroom' AND r.date_event!='0000-00-00'
GROUP BY YEAR(r.date_event);

Do you know how can i do please ?
Regards
Chris

Hi,
You can join both the queries using UNION.

Thanks Daniel,

I tried it but i have some mysql errors...

Regards
Chris

Hello,
If you want we can also assist you with the Database queries as well. Please contact our Sales team via Contact us -> Request a Quote form, We'll look into it.

Hi,

Thanks for reply, i tried this

SELECT YEAR(e.enrollment_date_start) AS y, count(e.id) AS n
FROM #__admincrm_programs as p
LEFT JOIN #__admincrm_versions as v ON(v.programs_id=p.id)
LEFT JOIN #__admincrm_sessions as s ON(s.versions_id=v.id)
LEFT JOIN #__admincrm_enrollments AS e ON(e.sessions_id=s.id)
LEFT JOIN #__admincrm_results AS r ON(r.enrollments_id=e.id)
WHERE p.id IN(2,7,16,17) AND r.score>0 AND s.session_format='Distance learning' AND enrollment_date_start!='0000-00-00'
GROUP BY YEAR(enrollment_date_start)
UNION
SELECT YEAR(enrollment_date_start) AS y , count(e.id) AS m
FROM #__admincrm_programs as p
LEFT JOIN #__admincrm_versions as v ON(v.programs_id=p.id)
LEFT JOIN #__admincrm_sessions as s ON(s.versions_id=v.id)
LEFT JOIN #__admincrm_enrollments AS e ON(e.sessions_id=s.id)
LEFT JOIN #__admincrm_results AS r ON(r.enrollments_id=e.id)
WHERE p.id IN(2,7,16,17) AND r.score>0 AND s.session_format='Classroom' AND enrollment_date_start!='0000-00-00'
GROUP BY YEAR(enrollment_date_start);

Series name : n,m

An error appears when i click to preview : Number of Columns for series and Number of series should be equal

An idea, please ?

Thanks for help
Chris

Hi,
The first column returned from the query will be used for X-Axis values and all the other remaining columns will be used for series.