4

I have the following table in my database:

╔════════╦═════════════╦═══════════════╗
║  Name  ║ total_stars ║ total_reviews ║
╠════════╬═════════════╬═══════════════╣
║ Item A ║          27 ║             7 ║
║ Item B ║          36 ║             9 ║
║ Item C ║          27 ║             7 ║
║ Item D ║          30 ║             6 ║
║ Item E ║           0 ║             0 ║
║ Item F ║           0 ║             0 ║
║ Item F ║          15 ║             3 ║
╚════════╩═════════════╩═══════════════╝

I was looking at this article and trying to implement Bayesian rankings in PostgreSQL database.

The formula given for the rank is

br = ( (avg_num_votes * avg_rating) + (this_num_votes * this_rating) ) / 
(avg_num_votes + this_num_votes)

where:

  • avg_num_votes: The average number of votes of all items that have num_votes>0
  • avg_rating: The average rating of each item (again, of those that have num_votes>0)
  • this_num_votes: number of votes for this item
  • this_rating: the rating of this item

This is the query I came up with, but it is not working:

with avg_num_votes as (
      select AVG(total_reviews) 
           from business 
           where total_reviews != 0), 
       avg_rating as (
          select AVG(total_stars/total_reviews) 
          from business 
          where total_reviews != 0)
select * from business 
  order by ((avg_num_votes * avg_rating) + (total_stars)) / (avg_num_votes + total_reviews);

I am getting:

ERROR:  column "avg_num_votes" does not exist
3
  • Your CTE Appears to have the same name as a column.
    – swasheck
    Dec 20, 2012 at 3:45
  • +1 for having ASCII art in the post! +1 to Stackexchange for supporting it too! Dec 20, 2012 at 20:12
  • While ASCII art is really nice, it would be more practical to have a CREATE TABLE statement or a link to a demo on sqlfiddle in there. Jan 10, 2013 at 23:28

1 Answer 1

3

Here

with avg_num_votes as (
      select AVG(total_reviews) 
           from business 
           where total_reviews != 0), 

avg_num_votes is the name of the table. The column itself gets a nice auto-generated name (in this case, most probably avg), which is clearly not what you want and refer below. You can give a name to the column two ways:

with avg_num_votes as (
      select AVG(total_reviews) AS avg_num_votes
      ...

or

with avg_num_votes(avg_num_votes) as (
      select AVG(total_reviews) 
      ...

Choose whch one you prefer. The two only differ in readability.

But this is only a part of the problem. In the main query you have to refer the CTEs constructed with WITH. In order to get meaningful results, you have to join then on a column present in all tables - so, if you choose Name, then you have to do something like

with avg_num_votes(avg_num_votes) as (
      select AVG(total_reviews), Name
      from ...
      GROUP BY Name
      ...
select b.* 
from business b
    JOIN avg_num_votes v ON b.Name = v.Name
    JOIN avg_rating r ON b.Name = r.Name
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.