Calculate number of rows with having clause

I have 3 tables what I’m trying to achieve is to calculate exact number of rows for two kinds of queries.

The first one must count number of accounts which has exactly only one row in accounts_extra for specific service_id.

The second one must count number of accounts which has exactly only one row in accounts_extra and also trial has not ended for specific id

http://sqlfiddle.com/#!15/313db/3

Basically I get in the first query 0 which is correct but in second query I get 1 which is not correct.

I assume that subscription is optional so that’s why I get 1 in the second query what should I do to achieve 0 in the second query but still taken into consideration trial_ends_at

Answer

Your question is rather hard to follow, but I think this does what you are describing:

SELECT SUM( (cnt = 1)::int ) as count1,
       SUM( (cnt = 1 AND cnt2 > 0)::int ) as count2
FROM (SELECT a.id, COUNT(DISTINCT ae.id) AS cnt,
             COUNT(ans.id) as cnt2
      FROM accounts a JOIN
           accounts_extra ae
           ON a.id = ae.account_id LEFT JOIN
           account_number_subscriptions ans
           ON ans.account_id = a.id AND
              ans.trial_ends_at > now()
      WHERE a.service_id = '101' AND
            a.closed = false AND
            ae.created_at < '2019-07-01'
      GROUP BY a.id
     ) a;