SQL Grouping with No Duplicates

Question

Here is the output. No problem here. Exactly what I want. But I added the DISTINCT ID to remove duplicates and that works in each grouped month.

MN   | CNT
====================
1    |  1
10   |  2
11   |  5
12   |  5

SELECT EXTRACT(MONTH FROM TRUNC(HDATE)) as MN, COUNT(DISTINCT ID) as CNT
    FROM Schema.TRAVEL
        WHERE (ARR = '2' OR ARR = '3')
        AND
        HDATE BETWEEN to_date('2015-10-01', 'yyyy-mm-dd') AND to_date('2016-09-30', 'yyyy-mm-dd')
GROUP BY EXTRACT(MONTH FROM TRUNC(HDATE));

But I can still possibly have duplicates that span more than each month. So if I have a record in October and another in November with the same ID - I want to only count this once - that is my issue

So over the course of a year or any time period - an ID only gets counted once...but I still need to maintain the monthly groupings and output...

??

You need to specify to which month should the id count if there are multiple values over more than 1 month. — Kamil Gosciminski
– Kamil Gosciminski, Commented Jan 7, 2016 at 17:07

Gordon Linoff · Accepted Answer · 2016-01-07 17:08:02Z

1

In other words, you want to count each id in the first month where it appears.

SELECT EXTRACT(MONTH FROM TRUNC(HDATE)) as MN, COUNT(DISTINCT ID) as CNT
FROM (SELECT id, MIN(HDATE) as HDATE
      FROM Schema.TRAVEL t
      WHERE ARR IN  '2', '3') AND
            HDATE BETWEEN  DATE '2015-10-01' AND DATE '2016-09-30'
      GROUP BY id
     ) t
GROUP BY EXTRACT(MONTH FROM TRUNC(HDATE));

Note: If an id appears before '2015-10-01', this will still count the id in the first month it appears after that date. If you don't want such an id counted at all, move the HDATE comparison to the outer query.

answered Jan 7, 2016 at 17:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ronald Over a year ago

Yes - this works great - what I was looking for. thanks. removed the duplicates...

Ronald Over a year ago

After thinking about this - I only want an ID counted once - even if its outside the date range - because it will be part of another query... If there are 4 records with the same ID and different HDATE - I only want to count it once - regardless of whether it falls in the date or not ...

Collectives™ on Stack Overflow

SQL Grouping with No Duplicates

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related