Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Your CTE Appears to have the same name as a column. –  swasheck Dec 20 '12 at 3:45
    
+1 for having ASCII art in the post! +1 to Stackexchange for supporting it too! –  trygvis Dec 20 '12 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. –  Erwin Brandstetter Jan 10 '13 at 23:28

1 Answer 1

up vote 3 down vote accepted

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
...
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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