[SOLVED] Simple 'select' with AVG

Hi folks,

I have a really easy SQL statement:


SELECT AVG(rating) FROM `table` WHERE `value`= 156

[/indent]Actually I work with AR, the simple way to access the database.

without ‘AVG’ the select part of ‘CDbCriteria’ is absolut easy:


$crit->select = 'rating';

[/indent]That work’s fine. And now I only want to add the ‘AVG’ SQL statement

Adding ‘AVG’ , the result is wrong:


$crit->select = 'AVG(rating)';

[/indent]Is it really neccesary to use a Statistical Query as descriped in the guide?

I can’t believe it, because the SQL statement is so sipmple …

Thanx for comments.


[indent] [/indent]

You need a model attribute that will hold the value of AVG() and use an alias in your SQL statement:

public $avg;


'SELECT AVG(rating) as `avg`...'

Hi Mike,

thank you for your comment.

I added the variable like descriped:


 public $avg; 

[/indent]and also within the select criteria:


$crit->select = "AVG(rating) as 'avg'";

[/indent]When I copy the SQL - Statement from the trace console it looks like:


SELECT AVG(rating) as 'avg' FROM `table` `t` WHERE value = 156 LIMIT 1

[/indent]and get the result in the mysql frontend:




[/indent]That’s fine & correct.

But I have problems to get the result in my code.

Now I show you the code, maybe I’m wrong in an other line …


$bw = new CBewertung(); // my class

$crit = new CDbCriteria();


$crit->condition = 'produkt_id = :pid AND anleitung = :anl';

$crit->params = array(':pid'=>$id, ':anl'=>$typ);


 $crit->select = "AVG(stars) as 'avg'"; 

 $bewertung = $bw->find($crit);

 echo $bewertung['stars'];

[/indent]In this case echo is empty.

Changing the select-stament:


 $crit->select = "stars"; 

[/indent]The result is 4 (that’s right, because the first value in the db is 4.)

Hope that helps to explain my challenge …

[indent] [/indent]

Have you tried setting the select to a CDbExpression?

$crit->select = new CDbExpression( 'AVG( rating )' );


Same think: SQL stament (trace mode) looks perfect and works with mysql frontend.

But my code echo is still empty


There must be a ‘stupid’ mistake …

You’re selecting AVG( stars ) AS rating, which means the column from the DB will be titled rating. This doesn’t line up with the active record property, if you select as AVG( stars ) as stars does it work?

Thank you!

First: I changed it from rating to stars and doesn’t explained it in my threat. …

But your tip was perfect, the solution is:


$crit->select = new CDbExpression('AVG(stars) as stars');

[/indent]That means, it is nessecary to build up the select statement via CDbExpression.

That could work for all these thinks, like MIN, MAX etc. - I will check it …

Thank you again.

[indent] [/indent]