Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit



SELECT 1


create table post (postid int, title varchar, author int, created timestamp);
create table votes (postid int, username varchar, vote int);

insert into post values (1,'test1',1,now());
insert into post values (2,'test2',1,now());

insert into votes values(1,'A',1);
insert into votes values(1,'B',-1);
insert into votes values(2,'B',-1);

CREATE TABLE



CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT 
post.postID as postID, 
post.title as title, 
post.author as author,
COALESCE(sum(votes.vote), 0) as voteCount, 
post.created as created,
COALESCE(sum(votes.vote) filter (where votes.username= '1'), 0) as voteCount

       FROM post LEFT JOIN votes ON post.postID = votes.postID 
       GROUP BY 1,2,3,5 ORDER BY 4 DESC


postid
title
author
votecount
created
votecount


1
test1
1
0
2024-04-23 11:10:29.832765
0

2
test2
1
-1
2024-04-23 11:10:29.832765
0



SELECT 2


SELECT 
    p.postID as postID, 
    p.title as title, 
    p.author as author,
    p.created as created,
    COALESCE(sum(v1.vote), 0) as voteCount, 
    COALESCE(v2.vote , 0) as userVote  -- in '' just provide username for current login user
FROM post p
LEFT JOIN votes v1 ON p.postID = v1.postID 
LEFT JOIN votes v2 on p.postID = v2.postID and v2.username='username'
GROUP BY 1,2,3,4,v2.vote
ORDER BY 5 DESC


postid
title
author
created
votecount
uservote


1
test1
1
2024-04-23 11:10:29.832765
0
0

2
test2
1
2024-04-23 11:10:29.832765
-1
0



SELECT 2

postid	title	author	votecount	created	votecount
1	test1	1	0	2024-04-23 11:10:29.832765	0
2	test2	1	-1	2024-04-23 11:10:29.832765	0