I have a query which returns a result set which looks like this:
A | B
---------------------
1 | a
2 | a
1 | b
1 | c
3 | d
A|B
is unique in the results (i.e. there will only ever be one 1|a
row)
I need to write two queries based on these results. The first:
given a value for A
(say 1
), count the number of B
s who only have a single row; with a 1
in the A
column.
so in the above data set, id expect to be returned the value 2
, because B
values b
and c
only have one row each, where those rows have a 1
in the A
column. d
would not be counted because it corresponds to a 3
and a
would not be returned because it has an additional row with a 2
.
The second query is the opposite to the above.
Given a value for A
(say 1
) count the number of B
s who have 2 or more rows, where one of the rows has a 1
in the A
column.
So in the above data id expect to be returned 1
, because the B
value a
is the only one that has multiple rows, with one of them with a 1
in the A column.
With CTEs and windowed aggregate functions, it could be done like this:
WITH
sampledata (A, B) AS (
SELECT 1, 'a' UNION ALL
SELECT 2, 'a' UNION ALL
SELECT 1, 'b' UNION ALL
SELECT 1, 'c' UNION ALL
SELECT 3, 'd'
),
counted AS (
SELECT
A,
B,
COUNT(*) OVER (PARTITION BY B) B_count
FROM sampledata
)
SELECT
COUNT(*)
FROM counted
WHERE A = 1
AND B_count = 1 /* change to "> 1" to get the other result */