Postgresql Faulty Syntax on select/join/group

What about the following is not proper syntax for Postgresql?

select p.*, SUM(vote) as votes_count 
FROM votes v, posts p 
where p.id = v.`voteable_id` 
  AND v.`voteable_type` = 'Post' 
group by v.voteable_id 
order by v开发者_开发问答otes_count DESC limit 20

I am in the process of installing postgresql locally but wanted to get this out sooner :)

Thank you

MySQL is a lot looser in its interpretation of standard SQL than PostgreSQL is. There are two issues with your query:

1. Backtick quoting is a MySQL thing.
2. Your GROUP BY is invalid.

The first one can be fixed by simply removing the offending quotes. The second one requires more work; from the fine manual:

When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.

This means that every column mentioned in your SELECT either has to appear in an aggregate function or in the GROUP BY clause. So, you have to expand your p.* and make sure that all those columns are in the GROUP BY, you should end up with something like this but with real columns in place of p.column...:

select p.id, p.column..., sum(v.vote) as votes_count
from votes v, posts p
where p.id = v.voteable_id
  and v.voteable_type = 'Post'
group by p.id, p.column...
order by votes_count desc
limit 20

This is a pretty common problem when moving from MySQL to anything else.