# Bayesian ranking

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) ) /
``````

where:

• avg_rating: The average rating of each item (again, of those that have num_votes>0)
• 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)
where total_reviews != 0),
avg_rating as (
select AVG(total_stars/total_reviews)
where total_reviews != 0)
``````

I am getting:

``````ERROR:  column "avg_num_votes" does not exist
``````
-
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! – Trygve Laugstøl 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

Here

``````with avg_num_votes as (
select AVG(total_reviews)
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 (
...
``````

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.*