Count rows using SQL query with JOIN

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, exchange_full_name)

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:

WITH pairs AS(SELECT *
FROM common.active_pairs cp
GROUP BY pair, cp.exchange_id, exchange_id, cp.id, cp.min_order_exchange_id, cp.exchange_active, cp.prime_active, cp.otc_active, cp.marketmaking_active, cp.arbitrage_active
HAVING COUNT(pair) = 1)
SELECT p.pair, p.exchange_id, e.exchange_long_name
FROM pairs p
INNER JOIN common.exchanges e ON(p.exchange_id = e.exchange_id);

Query to count rows to calculate pagination:

WITH pairs AS(SELECT DISTINCT COUNT(*) OVER () counter
FROM common.active_pairs cp
GROUP BY pair, cp.exchange_id, exchange_id
HAVING COUNT(pair) = 1)
SELECT e.exchange_id
FROM pairs p
INNER JOIN common.exchanges e ON(p.exchange_id = e.exchange_id);

I get error [42703] ERROR: column p.exchange_id does not exist for p.exchange_id.

Is there some way to edit the query to count the rows using JOIN?

Answer 1

Count total number of rows per pair and total number of distinct exchange_id per pair. Use 1 inside COUNT() means all rows.

-- PostgreSQL
select ap.pair
     , COUNT(1) total_exchange_id
     , COUNT(DISTINCT ap.exchange_id) total_distinct_exchange_id
from active_pairs ap
inner join exchanges e
        on ap.exchange_id = e.exchange_id 
       -- and ap.exchange_id = 2 
group by ap.pair

Please check this url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=c7c945ad52da59d307ed7f1c29da9582

Count pair wise rows and distinct rows then sum of rows to get the result

-- PostgreSQL
SELECT SUM(t.total_exchange_id) total
     , SUM(t.total_distinct_exchange_id) distinct_total
FROM (select ap.pair
           , COUNT(1) total_exchange_id
           , COUNT(DISTINCT ap.exchange_id) total_distinct_exchange_id
      from active_pairs ap
      inner join exchanges e
              on ap.exchange_id = e.exchange_id 
            -- and ap.exchange_id = 2 
      group by ap.pair) t

Please check this url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=5df605cf1544c7433f840f4be5d80642