Efficient SQL query to add totals and existence aggregates from a many-many table

Question

I have two tables

Magazines with fields id, name;
Subscriptions with fields subscriber_id and magazine_id, which are foreign keys from Magazines and another table of subscribers.

I would like to extend the Magazines table to have:

a third column listing its total number of subscribers and
a fourth column subscribed which has a boolean value which is true iff a record exists in Subscriptions with my subscriber_id (we can say I am subscriber_id=1).

Is there a canonical way to solve these problems? If not how can I do this efficiently?

To get the subscribers column, the simplest thing I thought of doesn't list magazines with no subscribers. (I did read here but couldn't see how it's related)

SELECT magazine_id, COUNT(*)
FROM subscriptions GROUP BY magazine_id
ORDER BY magazine_id ASC;

So instead I did something a bit hacky and probably inefficient:

SELECT
 magazines.id,
 CASE WHEN NOT EXISTS(
  SELECT * FROM subscriptions WHERE magazines.id=magazine_id
 ) THEN 0
 ELSE COUNT(*) END AS subscribers
FROM
 magazines
LEFT JOIN
  subscriptions
ON
 magazines.id = subscriptions.magazine_id
GROUP BY
 magazines.id
ORDER BY
 magazines.id ASC;

For the boolean fourth column, I got a solution by left joining the previous result with subscriptions again, and doing case when is null for the value of the subscriber_id.

I am using PostgreSQL. I'm not a student but I'd like to learn how to solve this problem, magazines/subscribers is just an example. I know only basics of SQL.

score 0 · Answer 1 · answered Jul 16 '19 at 00:48

The first thing would be to take the query you have for subscriber counts and join that on to the magazine table.

so

SELECT id, COUNT(*) AS subscriber_count
FROM subscriptions GROUP BY magazine_id

becomes

SELECT mag.id, COALESCE(subs.subscriber_count, 0) AS subscribers
FROM magazines mag
LEFT JOIN (
    SELECT magazine_id, COUNT(*) AS subscriber_count 
    FROM subscriptions GROUP BY magazine_id) subs ON subs.magazine.id = mag.id
ORDER BY mag.id ASC

As for adding on if you are a subscriber, there are a few ways to go about that: 1) similar to above, create a query and join that to magazines

--Query:
SELECT DISTINCT magazine_id FROM subscriptions WHERE subscriber_id = 1

--Joined:
SELECT 
    mag.id, 
    COALESCE(subs.subscriber_count, 0) AS subscribers,
    CASE WHEN mysubs.magazine_id IS NULL THEN 0 ELSE 1 END AS my_subscription
FROM magazines mag
LEFT JOIN (
    SELECT magazine_id, COUNT(*) AS subscriber_count 
    FROM subscriptions GROUP BY magazine_id) subs ON subs.magazine_id = mag.id
LEFT JOIN (
    SELECT DISTINCT magazine_id FROM subscriptions WHERE subscriber_id = 1
    ) mysubs ON mysubs.magazine_id = mag.id
ORDER BY mag.id ASC

2) If subscription table is unique on magazine_id AND subscriber_id you can join directly without a sub-query:

SELECT 
    mag.id, 
    COALESCE(subs.subscriber_count, 0) AS subscribers,
    CASE WHEN mysubs.magazine_id IS NULL THEN 0 ELSE 1 END AS my_subscription
FROM magazines mag
LEFT JOIN (
    SELECT magazine_id, COUNT(*) AS subscriber_count 
    FROM subscriptions GROUP BY magazine_id) subs ON subs.magazine_id = mag.id
LEFT JOIN subscriptions mysubs on mysubs.magazine_id = mag.id AND subscriber_id = 1
ORDER BY mag.id ASC

3) adding the conditional to the subscription sub-query by using the MAX aggregation on the bool

SELECT 
    mag.id, 
    COALESCE(subs.subscriber_count, 0) AS subscribers,
    COALESCE(subs.mysub, 0) AS my_subscription
FROM magazines mag
LEFT JOIN (
    SELECT magazine_id, COUNT(*) AS subscriber_count,
        MAX(CASE WHEN subscriber_id = 1 THEN 1 ELSE 0 END) AS mysub
    FROM subscriptions GROUP BY magazine_id) subs ON subs.magazine_id = mag.id
ORDER BY mag.id ASC

score 0 · Answer 2 · answered Jul 16 '19 at 01:01

If you just want the magazine id, you can do:

select magazine_id, count(*) as num_subscribers,
       max(case when subscriber_id = 1 then 1 else 0 end) as my_subscriber
from subscriptions s
group by magazine_id;

That is, no join is necessary if you only want the magazine id.

Note: this only returns magazines with subscribers.

This can readily be extended with a left join:

select m.mag, count(s.magazine_id) as num_subscribers,
       max(case when s.subscriber_id = 1 then 1 else 0 end) as my_subscriber
from magazines m left join
     subscriptions s
     on s.magazine_id = m.mag_id
group by m.mag_id;

Efficient SQL query to add totals and existence aggregates from a many-many table

2 Answers2