CODEWITHSUNDEEP
mysql
madhur
madhur

Reputation: 1001

Getting average of selected values in mysql

I have a table structure 

 id  group name points
  1   1     a     10
  2   1     b      9
  3   2     c      7

and so on..

I am writing a query which gives me an array of names and avg of all the points for seleceted rows where group matches the value

for group_list = [1] want a results like this [name: ['a','b'], median:[9.5]]

I have tried like this 

$group_list = [1];                                  
        createQueryBuilder()
        ->select('x.name as name, x.AVG(points) as median')
        ->from('myTable', 'x')
        ->where('x.group IN(:groupList)')
        ->setParameter('groupList', $group_list)
        ->getQuery()
        ->getResult();

Need some help with this

Upvotes: 1

Views: 196

Answers (2)

Shadow
Shadow

Reputation: 34244

You are combining 2 distinct requirements into a single sql statement and this causes the problem.

The average of points is a single value per group or per all records, while the names are a list. You can combine the 2 into a single query by repeating the averages across the names, however, it just generates an overhead.

I would simply run a query to get the list of usernames and a separate one to get the average points (either grouped by groups or across all groups, this is not clear from the question).

This solution is so simple, that I do not think I need to provide any code.

Alternatively, you can use MySQL's group_concat() function to get the list of names per group into in single value in comma separated list (you can use any other separator character in place of comma). In this case it is more worthwile to combine the 2 in a single query:

select group_concat(`name`) as names, avg(`points`) as median
from mytable
where `group` in (...)

If you want names from more than one groups, then add group field to the select and group by lists:

select `group`, group_concat(`name`) as names, avg(`points`) as median
from mytable
where `group` in (...)
group by `group`

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You should add a group by 

 ->groupBy('x.`group`')

Upvotes: 0

Related Questions