Using SQL Aggregate Functions With Multiple Joins

aggregate-functions, join, left-join, postgresql, sql

Solution

The easy fix to your query is to use `distinct`:

SELECT user_id, COUNT(distinct item_sold_id), COUNT(distinct item_bought_id)
FROM user
LEFT JOIN item_sold ON sold_user_id=user_id
LEFT JOIN item_bought ON bought_user_id=user_id
WHERE user_date_created > '2014-01-01'
GROUP BY user_id;

However, the query is doing unnecessary work. If someone has 100 items bought and 200 items sold, then the join produces 20,000 intermediate rows. That is a lot.

The solution is to pre-aggregate the results or use a correlated subquery in the `select`. In this case, I prefer the correlated subquery solution (assuming the right indexes are available):

SELECT u.user_id,
       (select count(*) from item_sold s where u.user_id = s.sold_user_id),
       (select count(*) from item_bought b where u.user_id = b.bought_user_id)
FROM user u
WHERE u.user_date_created > '2014-01-01';

The right indexes are `item_sold(sold_user_id)` and `item_bought(bought_user_id)`. I prefer this over pre-aggregation because of the filtering on the `user` table. This only does the calculations for users created this year -- that is harder to do with pre-aggregation.

Problem

I am attempting to use multiple aggregate functions across multiple tables in a single SQL query (using Postgres). My table is structured similar to the following: ``` CREATE TABLE user (user_id INT PRIMARY KEY, user_date_created TIMESTAMP NOT NULL); CREATE TABLE item_sold (item_sold_id INT PRIMARY KEY, sold_user_id INT NOT NULL); CREATE TABLE item_bought (item_bought_id INT PRIMARY KEY, bought_user_id INT NOT NULL); ``` I want to count the number of items bought and sold for each user. The solution I thought up does not work: ``` SELECT user_id, COUNT(item_sold_id), COUNT(item_bought_id) FROM user LEFT JOIN item_sold ON sold_user_id=user_id LEFT JOIN item_bought ON bought_user_id=user_id WHERE user_date_created > '2014-01-01' GROUP BY user_id; ``` That seems to perform all the combinations of (item_sold_id, item_bought_id), e.g. if there are 4 sold and 2 bought, both COUNT()s are 8. How can I properly query the table to obtain both counts?

Original source