CODEWITHSUNDEEP
phpsql
rhill45
rhill45

Reputation: 569

SQL command to get the count from an average (ie what is n)

Two tables: 1. stories, one column lists over 10,000 story titles with other columns including author, date, category etc. 'id'is the column that is a unique identifyer (auto incrememnting for each story) 2. ratings. This table records star ranking for each of the stories. So this table, has 3 columns, a auto incrementing unique id, the id from table number 1 (which in table 2 is called storyidr)/, the rank value.

So i would like to report the average rating and the total number of ratings for each story.

I've used sql JOIN and I can get the average rating to report fine. 

          SELECT s.*, 
          ROUND(AVG(r.rank),0) 
          AS avrank 
          FROM stories s 
          LEFT JOIN ratings 
          AS r 
          ON r.storyidr = s.id 
          GROUP BY s.id 
          ORDER BY RAND() 
          LIMIT 200;";

Getting the count is another story. i'm trying COUNT and UNION. Nothing is working. Is there a way to 'extract' the 'value of n' from the average value that is already being queried?

knowing that average=(sum/n)

I don't have to do it this way. If i could add additional SQL queries to the current one to get the count that would be just fine. I'm just not seeing how to add the count function to the current script?

With suggestions:

$query="SELECT s.*, COUNT(r.rank) AS rkct ROUND(AVG(r.rank),0) AS avrank FROM stories s LEFT JOIN ratings AS r ON r.storyidr = s.id GROUP BY s.id ORDER BY RAND() LIMIT 5;";$result=mysqli_query($connection,$query); 

<?php while ($data = mysqli_fetch_assoc($result)):$id = $data['id'];$author = $data['author'];$email = $data['email'];$title = $data['title'];$img_link = $data['img_link'];$smaller_img = $data['smaller_img'];$story_link = $data['story_link'];$page_path = $data['page_path'];$tag_alt = $data['tag_alt'];$category = $data['category'];$avgrate = $data['avrank'];$rankcount = $data['rkct'];

The suggestions are giving me the same error: Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in intro.php on line 88.

this is line 88: $avgratep = "Avg rating: " . $avgrate . "/5";

seems like adding the count is making the avrank value nothing or non-numeric?

Upvotes: 1

Views: 102

Answers (1)

Mureinik
Mureinik

Reputation: 311583

Just call the count function in the same way you call avg:

  SELECT s.*, 
  ROUND(AVG(r.rank),0)  AS avrank,
  COUNT(*) AS countrank
  FROM stories s 
  LEFT JOIN ratings 
  AS r 
  ON r.storyidr = s.id 
  GROUP BY s.id 
  ORDER BY RAND() 
  LIMIT 200;

Upvotes: 1

Related Questions