sql query group by name and average

sql query group by name and average

by Mike Glazebrook -
Number of replies: 16

I am new to sql and am having a hard time figuring out what to query in order to get a google pie chart functioning correctly.

SELECT `rank`, `firstname`, `email`,`username`
FROM `mdl_user`, `mdl_questionnaire_response_rank`
WHERE 1
GROUP BY `email`

That is how I am running the query right now. I have attached a screen shot of how it displays right now.

It is exactly how I want it other than when I grouped it by `email` it forced all the ranks to be the same number.

I want to group it the way that it is but then show the AVG rank per user in the users section of the pie chart.

so Alex may have a 45% section of chart while Mike may have 75%.

If I get that much I will be happy but I will also eventually want to make the chart so that only the user will be able to see their average slice of the pie chart to an average of everyone else. 

If you know how to call the current user only to show and then average of everyone else that would be awesome.

Let me know if you need any more info.

Thanks in advance!

Attachment Screen Shot 2013-07-03 at 12.29.42 PM.png
Average of ratings: -
In reply to Mike Glazebrook

Re: sql query group by name and average

by Darko Miletić -

Your query is not correct.

Average of ratings: -
In reply to Darko Miletić

Re: sql query group by name and average

by Darko Miletić -

This would be the correct base query

SELECT res.id, res.rank, u.firstname, u.email, u.username
FROM
mdl_questionnaire_response_rank res,
mdl_questionnaire_response rp,
mdl_questionnaire_attempts att,
mdl_user u
WHERE
res.response_id = rp.id
and
att.rid = rp.id
and
att.userid = u.id

Average of ratings: Useful (1)
In reply to Darko Miletić

Re: sql query group by name and average

by Mike Glazebrook -

You know I replied to your post asking how to get all the users and it turned out that I had already had them. Not sure what I was thinking. This is exactly what I needed.

Thanks a ton!

Average of ratings: -
In reply to Mike Glazebrook

Re: sql query group by name and average

by Mike Glazebrook -

do you know how I could get the current user to show compared to an average of all the others? The label would then have to change from a name to something like average. If not no big deal. I have gotten a great start from you smile

Average of ratings: -
In reply to Mike Glazebrook

Re: sql query group by name and average

by Mike Glazebrook -

the image is more so the reason I ask for the query for a singel user. I am trying to make a line chart that charts a users responses. I want one user (current / logged in) to be able to see their responses / rank over a period of time.

Thanks again!

Attachment Screen Shot 2013-07-03 at 3.29.58 PM.png
Average of ratings: -
In reply to Mike Glazebrook

Re: sql query group by name and average

by Darko Miletić -

If you need to filter for current user than just add another where condition

u.id = <current user id>

in PHP it would be something like:

global $USER, $DB;

$sql = "SELECT res.id, res.rank, u.firstname, u.email, u.username, rp.submitted
FROM
{questionnaire_response_rank} res,
{questionnaire_response} rp,
{questionnaire_attempts} att,
{user} u
WHERE
res.response_id = rp.id
AND
att.rid = rp.id
AND
att.userid = u.id
AND
u.id = :currentuser";

$user = $DB->get_records_sql($sql, array('currentuser' => $USER->id));

Average of ratings: Useful (1)
In reply to Darko Miletić

Re: sql query group by name and average

by Mike Glazebrook -

Thanks again! This is really going to help. I really appreciate it smile

Average of ratings: -
In reply to Darko Miletić

Re: sql query group by name and average

by Mike Glazebrook -

Ok maybe you can help me out smile I am stuck again. I have attached my code and the error I am getting is such:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /homepages/14/d271388824/htdocs/moodle/moodle/mod/page/getdatasymptoms_line.php on line 42
{"cols":[{"First Name":"firstname","type":"string"},{"Email":"email","type":"string"},{"Name":"name","type":"string"},{"Symptom Level":"rank","type":"number"}],"rows":[]}

You will also see that I am not passing any info into the rows anymore. 

Thanks again for your help. I feel like I am learning a ton it is just slow going smile

Average of ratings: -
In reply to Mike Glazebrook

Re: sql query group by name and average

by Darko Miletić -

Dude, you use mysql api to connect to database. That is strictly prohibited in Moodle. YOU MUST USE DB API! The code I provided uses Moodle DB API.

See this page for more details

http://docs.moodle.org/dev/Data_manipulation_API

Average of ratings: -
In reply to Darko Miletić

Re: sql query group by name and average

by Mike Glazebrook -

Oh geez. I didn't mean to break any rules that's for sure. I am just very inexperienced with moodle as wells as php and mysql for that matter. I will certainly get it changed. Thanks again for continuing to help me out with this. It is really appreciated!

Average of ratings: -
In reply to Darko Miletić

Re: sql query group by name and average

by Mike Glazebrook -

Well I have switched to the DB API method but unfortunately I don't think I am talking to the database anymore. I have attached my code again and this is the current error: mysql_fetch_assoc() expects parameter 1 to be resource, string given in /homepages/14/d271388824/htdocs/moodle/moodle/mod/page/getdatasymptoms_line.php on line 38
{"cols":[{"First Name":"firstname","type":"string"},{"Symptom Level":"rank","type":"number"}],"rows":[]}

I am not passing any data to the rows anymore.

Do I need to include the require('../../config.php'); at the top? I was able to make an error go away by doing so: Call to a member function get_records_sql() on a non-object in /homepages/14/d271388824/htdocs/moodle/moodle/mod/page/getdatasymptoms_line.php on line 20

Too bad I was using the wrong database connection. I feel like I am starting from scratch now.

Thanks again!

 

Average of ratings: -
In reply to Darko Miletić

Re: sql query group by name and average

by Mike Glazebrook -

Ok I have some updated code here. This is the warning that I am now getting:

Warning: mysql_query() expects parameter 1 to be string, array given in /homepages/14/d271388824/htdocs/moodle/moodle/mod/page/getdatasymptoms_line.php on line 22

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, null given in /homepages/14/d271388824/htdocs/moodle/moodle/mod/page/getdatasymptoms_line.php on line 38
{"cols":[{"First Name":"firstname","type":"string"},{"Symptom Level":"rank","type":"number"}],"rows":[]}

I don't think that the DB API accepts mysql_query and mysql_fetch_assoc which look as though they are depreciated anyway. Can you help me with an alternative to using that method? 

Average of ratings: -
In reply to Mike Glazebrook

Re: sql query group by name and average

by Darko Miletić -

Forget about mysql API. Here is the full example

require('../../config.php');
global $USER, $DB;

$sql = "SELECT res.id, res.rank, u.firstname, u.email, u.username, rp.submitted
FROM
{questionnaire_response_rank} res,
{questionnaire_response} rp,
{questionnaire_attempts} att,
{user} u
WHERE
res.response_id = rp.id
AND
att.rid = rp.id
AND
att.userid = u.id
AND
u.id = :currentuser";

$userdata = $DB->get_records_sql($sql, array('currentuser' => $USER->id));

header('Content-Type: application/json; charset=UTF-8');
echo json_encode($userdata, JSON_PRETTY_PRINT);

Average of ratings: Useful (1)
In reply to Darko Miletić

Re: sql query group by name and average

by Darko Miletić -

get_records_sql returns to you array of objects, where each object represents an individual record from the query. Than you just encode that into json and done.

Or you can do foreach on $userdata and transform that into some custom array which you can later encode.

And do not use ending tag in PHP

Average of ratings: Useful (1)
In reply to Darko Miletić

Re: sql query group by name and average

by Mike Glazebrook -

Took me a little while but I finally got it! Could you be so kind as to help me convert the rp.submitted number to mm/dd/yyyy? I will post back if I figure it out. Thanks again!

Attachment Screen Shot 2013-07-10 at 4.27.20 PM.png
Average of ratings: -