SQLize Online / PHPize Online  /  SQLtest Online

create table posts ( id int auto_increment primary key, body varchar(1024) ); create table likes ( id int auto_increment primary key, post_id int, direction enum('UP', 'DOWN'), index (post_id), foreign key (post_id) references posts (id) ); insert into posts (body) values ('First post'), ('Second post'), ('One more post'); insert into likes (post_id, direction) values (1, 'UP'), (1, 'UP'), (2, 'UP'), (2, 'DOWN'), (2, 'DOWN'); select posts.id, posts.body, sum( case direction when 'UP' then 1 when 'DOWN' then -1 else 0 end) likes from posts left join likes on likes.post_id = posts.id group by posts.id, posts.body order by likes desc ;