Contact Us Today! | vcare@wdmtech.com

WDMtech

Topic-icon 2 series from db

More
24 Nov 2017 10:36 #5170

Please contact our Sales team for the purpose via Contact us -> Request a Quote form.


Thanks
Daniel

Please Log in or Create an account to join the conversation.

  • Julien Camus
  • Julien Camus's Avatar Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
More
24 Nov 2017 09:49 #5168

Ok i understand and could you show me where is my error ?
Thanks
Chris

Please Log in or Create an account to join the conversation.

More
24 Nov 2017 06:05 #5166

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.


Thanks
Daniel

Please Log in or Create an account to join the conversation.

  • Julien Camus
  • Julien Camus's Avatar Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
More
23 Nov 2017 14:40 #5165

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

Please Log in or Create an account to join the conversation.

More
23 Nov 2017 11:27 #5163

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.


Thanks
Daniel

Please Log in or Create an account to join the conversation.

  • Julien Camus
  • Julien Camus's Avatar Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
More
22 Nov 2017 11:02 #5152

Thanks Daniel,

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

Regards
Chris

Please Log in or Create an account to join the conversation.

More
22 Nov 2017 10:28 #5149

Hi,
You can join both the queries using UNION.


Thanks
Daniel

Please Log in or Create an account to join the conversation.

  • Julien Camus
  • Julien Camus's Avatar Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
More
22 Nov 2017 09:55 #5147

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

Please Log in or Create an account to join the conversation.