Why does my Count(Distinct) result change when I add an additional group by column?
Here's my scenario.
create table cash_rcpts(pmt_method varchar2(30), rcpt_id number(15), trx_id number(15));
insert into cash_rcpts values ('Pmt Method 1',673,3615);
insert into cash_rcpts values ('Pmt Method 1',674,3616);
insert into cash_rcpts values ('Pmt Method 2',675,3615);
insert into cash_rcpts values ('Pmt Method 3',676,3617);
insert into cash_rcpts values ('Pmt Method 3',676,3618);
When I run "select count(distinct trx_id) as trx_cnt from cash_rcpts" I get 4 as expected.
TRX_CNT
4
However, if I add pmt_method to the query, which requires a group by, my total trx_cnt is now 5.
select pmt_method, count(distinct trx_id) as trx_cnt from cash_rcpts group by pmt_method;
PMT_METHOD TRX_CNT
Pmt Method 1 2
Pmt Method 3 2
Pmt Method 2 1
I was expecting the total trx cnt to remain at 4, but it is 5. Why is that? I still have 4 distinct values.