Postgres count isn't running accurate result-CodePudding

I have this Postgres query to return a count from 2 columns by querying from multiple tables.

Here is the example:

SELECT
        subscribers.email,
        COUNT(campaign_views.subscriber_id) AS views,
        COUNT(link_clicks.subscriber_id) AS clicks
        FROM campaign_views, link_clicks, subscribers, links
        WHERE campaign_views.campaign_id = 586
        AND link_clicks.campaign_id = 586
        AND link_clicks.link_id = links.id
        AND subscribers.channel = 'email'
        AND subscribers.id = campaign_views.subscriber_id
        AND subscribers.id = link_clicks.subscriber_id
        GROUP BY subscribers.id

The issue is that the COUNT result is the same for all of them like this:

views | clicks
16  16
6   6
2   2
4   4

Any ideas please?

CodePudding user response：

With

FROM campaign_views, link_clicks, subscribers, links

you are creating a CROSS JOIN.

Example with table a

value_a
-------
1
2
3

and table b

value_b
-------
a
b

With

SELECT
    *
FROM a, b

you're generating:

value_a | value_b
--------|--------
      1 | a
      1 | b
      2 | a
      2 | b
      3 | a
      3 | b

When you'd execute:

SELECT
    COUNT(a.value_a),
    COUNT(b.value_b)
FROM a,b

you'll receive:

count | count
------|------
    6 | 6

COUNT() returns all columns. In the result of your CROSS JOIN you generated 3 * 2 = 6 records for all columns. So, it makes sense, that all results you get are the same.

CodePudding user response：

So I managed to do the count with the accurate result.

WITH camps AS (
SELECT
    subscriber_id,
    COUNT(CASE WHEN subscriber_id = null THEN 0 ELSE 1 END) AS views 
FROM campaign_views
GROUP BY subscriber_id ),

links AS (
SELECT
    subscriber_id,
    COUNT(CASE WHEN subscriber_id = null THEN 0 ELSE 1 END) AS clicks 
FROM link_clicks
GROUP BY subscriber_id )

SELECT 
    subscribers.email, camps.views, links.clicks FROM camps, links, subscribers

WHERE camps.subscriber_id = links.subscriber_id
AND camps.subscriber_id = subscribers.id
AND links.subscriber_id = subscribers.id