Join table rows with empty key

Question

I have these 2 tables which I would like to query:

create table active_pairs
(
    pair                  text,
    exchange_id           integer
);


create table exchanges
(                        
    exchange_id         integer,
    exchange_full_name  text
);

INSERT INTO active_pairs (pair, exchange_id)

VALUES ('London/Berlin', 2),
       ('London/Berlin', 3),
       ('Paris/Berlin', 4),
       ('Paris/Berlin', 3),
       ('Oslo/Berlin', 2),
       ('Oslo/Berlin', 6),
       ('Huston/Berlin', 2);
       
INSERT INTO exchanges (exchange_id)

VALUES (2, 'Exchange 1'),
       (3, 'Exchange 2'),
       (4, 'Exchange 3'),
       (3, 'Exchange 21'),
       (2, 'Exchange 12'),
       (6, 'Exchange 11'),
       (2, 'Exchange 31');  

 

I use these queries to list all pairs:

Query to list items:

SELECT * FROM common.active_pairs ap
    INNER JOIN common.exchanges ce on ap.exchange_id = ce.exchange_id
WHERE ap.exchange_id = 1
GROUP BY pair, ap.exchange_id, ce.exchange_id, ap.id
HAVING COUNT(ap.pair) = 1;

I get as a result 172 rows.

Query to count rows to calculate pagination:

SELECT DISTINCT COUNT(*) OVER () counter
FROM common.active_pairs cp
    INNER JOIN common.exchanges ce on cp.exchange_id = ce.exchange_id
WHERE cp.exchange_id = 1
GROUP BY pair
HAVING COUNT(cp.pair) = 1

I get as a result 158 rows.

I should be able to get equal total numbers from both queries in order to calculate properly pagination.

Is it possible that records with empty exchange_id in giving the different result?

Answer 1

You do not group by active_pairs.exchange_id but only by the name of the pair (active_pairs.pair). If that name is not unique, pairs with the same name but different ID are counted as one in your pagination query.