Count rows with single record-CodePudding

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
);

INSERT INTO active_pairs (pair, exchange_id)

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

VALUES (2),
       (3),
       (4),
       (3),
       (2),
       (2);

I tried to use this SQL query to get records with single unique record for pair column:

SELECT COUNT(cp)
FROM active_pairs cp
    INNER JOIN exchanges ce on cp.exchange_id = ce.exchange_id
HAVING COUNT(pair) = 1

I should get as a result 2 for Oslo/Berlin and Huston/Berlin because they are unique.

But I get empty result. Can you advise me how I can fix this?

CodePudding user response：

If I understand correctly, you want to get values for pair that are used only once:

SELECT pair FROM active_pairs GROUP BY pair HAVING count(*) = 1

And then to count that:

SELECT count(*) FROM (
    SELECT pair FROM active_pairs GROUP BY pair HAVING count(*) = 1
)

(There are probably more efficient ways to do that, just this came to my mind first.)

CodePudding user response：

As far as I can understand the question, you would like to have something like this:

SELECT COUNT(DISTINCT pair)
FROM active_pairs;

If you want to see unique values in other columns then just add , COUNT(DISTINCT your_column)

CodePudding user response：

You can group by pair in the table active_pairs to filter only the pairs that occur only once and with COUNT() window function you can count them:

SELECT DISTINCT COUNT(*) OVER () counter
FROM active_pairs
GROUP BY pair
HAVING COUNT(*) = 1;

See the demo.

CodePudding user response：

-- what data is being queried (removed the WHERE)
SELECT COUNT(cp)
FROM active_pairs cp
INNER JOIN exchanges ce on cp.exchange_id = ce.exchange_id

first I removed the WHERE, but that gives:

count
14

-- added `GROUP BY pair`
SELECT pair, count(cp)
FROM active_pairs cp
GROUP BY pair
HAVING COUNT(*) = 1

Wait, I should use GROUP BY:

pair	count
Huston/Berlin	1
Oslo/Berlin	1

Then maybe add the last column to get 2?:

SELECT SUM(cp)
FROM (
   SELECT pair, count(cp) as cp
   FROM active_pairs cp
   GROUP BY pair
   HAVING COUNT(*) = 1
) x

Yes, result is: 2 !

see: DBFIDDLE