Oracle - Distinct with a Group by date

Question

I have a table like this in a Oracle 11g Database

TABLE REC
REC_ID NUMBER
CARD_ID NUMBER
REC_STATUS NUMBER
REC_DATE TIMESTAMP

I want to know how many cards have recharged in a given period, but I want this information grouped by date. If a card_id has already been counted on one date, it should not be counted on another next (distinct).

Here some test data

with
     REC ( REC_ID, CARD_ID, REC_STATUS, REC_DATE ) as (
       select '1', '100', '1', SYSTIMESTAMP - 5 from dual union all
       select '2', '100', '1', SYSTIMESTAMP - 5  from dual union all
       select '3', '200', '1', SYSTIMESTAMP - 5  from dual union all
       select '4', '100', '1', SYSTIMESTAMP - 4  from dual union all
       select '5', '300', '1', SYSTIMESTAMP - 4  from dual union all
       select '6', '200', '1', SYSTIMESTAMP - 4  from dual union all
       select '7', '100', '1', SYSTIMESTAMP - 3  from dual union all
       select '8', '400', '1', SYSTIMESTAMP - 3  from dual union all
       select '9', '400', '1', SYSTIMESTAMP - 3  from dual union all
       select '10', '400', '1', SYSTIMESTAMP - 2  from dual union all
       select '11', '300', '1', SYSTIMESTAMP - 2  from dual union all
       select '12', '100', '1', SYSTIMESTAMP - 2  from dual union all
       select '13', '400', '1', SYSTIMESTAMP - 2  from dual 
     )
-- end of test data

When I execute the query like this, i have the total count of 4, which is correct.

SELECT  
    COUNT(DISTINCT CARD_ID) COUNT 
FROM REC 
WHERE REC_STATUS = 1

Result

|COUNT|
|   4 |

But, when I try this way, i have the total count of 10. This is because when I use a "group by" on a date and use the "distinct" in the ID, the distinction will only work for the grouped date, not for the total period.

SELECT  
    TRUNC(REC_DATE) DAT, 
    COUNT(DISTINCT CARD_ID) COUNT
FROM REC 
WHERE REC_STATUS = 1
GROUP BY TRUNC(REC_DATE)

Result

DAT      | COUNT
14/06/19 |   2
13/06/19 |   3
15/06/19 |   3
12/06/19 |   2

What can I do to apply the distinct to the period total and still keep the grouping by date?

Sorry, not clear. What is your expected output for the above sample data? What is a "period?" — OldProgrammer

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-06-18T02:02:43

Perhaps you just want the earliest date for each card:

SELECT TRUNC(MIN_REC_DATE) as DAT, 
        COUNT(*) as COUNT
FROM (SELECT CARD_ID, MIN(REC_DATE) as MIN_REC_DATE
      FROM REC
      WHERE REC_STATUS = 1 
      GROUP BY CARD_ID
     ) R
GROUP BY TRUNC(MIN_REC_DATE);

Oracle - Distinct with a Group by date

1 Answers