Distinct count of one column based on the lack of frequency of another with unique values-CodePudding

I have the following data in a table

| Month | ID # | Transaction ID #|

Jan| 9991| 158423|

Jan| 9991| 537912|

Jan| 9988| 417823|

Jan| 9981| 659231|

Jan| 9980| 832239|

I want to find out the distinct count of the ID #s only when there's only 1 transaction associated with that ID. The transaction ids do not repeat and are all unique as well.

I have this base code here for all distinct values

select
date_trunc('year',transaction_date) as trx_month,
count (distinct profile_id) as profile_count,
count (fk_transaction_id) as trx_count,
count (distinct fk_transaction_id)/count (distinct profile_id) as trx_per_customer
from my_table
group by 1
order by 1;

I would like the following output

| Month | Total profiles with only 1 trx|

| Jan| 3|

CodePudding user response：

Using:

SELECT DISTINCT month,
       SUM(COUNT(*)) OVER(PARTITION BY month) AS cnt
FROM tab
GROUP BY month, ID
HAVING COUNT(DISTINCT transaction_id) = 1;

The trick here is to group by two columns month/id, use the HAVING to find only ID per month with one transaction.

Then the value is summed by month using windowed SUM.

db<>fiddle demo

CodePudding user response：

Using vanilla SQL approach

with cte as 

(select month, id
 from tab
 group by month, id
 having count(distinct transaction_id) = 1)

select month, count(id) as single_tranx_id_count
from cte
group by month;