CODEWITHSUNDEEP
sqlpostgresqljoin
vrde
vrde

Reputation: 937

Aggregate two different events tables with a single query

I have a simple schema with a table for the posts and two tables to track views and upvotes:

CREATE TABLE posts (
    id integer NOT NULL PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);

CREATE TABLE views (
    post_id integer,
    value integer
);

CREATE TABLE upvotes (
    post_id integer,
    value integer
);

Please note that the tuples in views and upvotes represent a generic number of views and upvotes. I want to aggregate the posts, showing the total amount of views and upvotes.

I thought it'd be simple, but unfortunately I'm failing.

This is my query:

SELECT posts.title,
       SUM(views.value) AS views,
       SUM(upvotes.value) AS upvotes

FROM posts
    LEFT OUTER JOIN views ON (posts.id = views.post_id)
    LEFT OUTER JOIN upvotes ON (posts.id = upvotes.post_id)

GROUP BY posts.id;

I understand why it's failing, but I really don't understand how to do it. If I need to run two different queries and aggregate the result on the application level, I'm fine with that. I just want to understand if it's possible with a single query

I created a sqlfiddle with my test case.

PS: I manage to make it work using just a single generic table to track events, but I'd like to keep the table separated. sqlfiddle here.

Upvotes: 2

Views: 144

Answers (2)

Mihai
Mihai

Reputation: 26784

SQL Fiddle

Do a JOIN between 2 separate selects

SELECT * FROM(SELECT posts.title,
       SUM(views.value) AS views
FROM posts
    LEFT OUTER JOIN views ON (posts.id = views.post_id)
GROUP BY posts.id)as x
JOIN(
SELECT posts.title,      
       SUM(upvotes.value) AS upvotes
FROM posts
    LEFT OUTER JOIN upvotes ON (posts.id = upvotes.post_id)
GROUP BY posts.id)as y on x.title=y.title

Upvotes: 1

John Woo
John Woo

Reputation: 263683

You can safely calculate the values in a subquery. This will avoid you from summing up duplicate value leading you to an invalid result.

SELECT  p.id,
        p.title,
        COALESCE(v.totalView, 0) totalView,
        COALESCE(u.totalUpvote, 0) totalUpvote
FROM    posts p
        LEFT JOIN
        (
            SELECT  post_id,
                    SUM(v.value) totalView
            FROM    views v
            GROUP   BY post_id
        ) v ON p.id = v.post_ID
        LEFT JOIN
        (
            SELECT  post_id,
                    SUM(u.value) totalUpvote
            FROM    upvotes u
            GROUP   BY post_id
        ) u ON p.ID = u.post_ID

Upvotes: 4

Related Questions