Oracle - Select rows Group By based on multiple columns

Question

I want to fetch duplicate rows for a particular date & time, group by both bname and txn_date AND txn_date up to Hours and Minutes (Seconds do not need to match)

For Example, I fetch the rows below for txn_date = 2020/07/28, txn_type = OPD, payment_mode = CREDIT and inv_num begins with AGL

Out of these, I want only rows with IDs from 1 - 4

ID |         txn_date       |   bname        
-----------------------------------------------
 1 | 2020/07/28 10:21:58 PM |  MRS R A W B VERNON
 2 | 2020/07/28 10:21:56 PM |  MRS R A W B VERNON
 3 | 2020/07/28 09:51:58 AM |  MRS TRIDENTZ
 4 | 2020/07/28 09:51:58 AM |  MRS TRIDENTZ
 5 | 2020/07/28 09:49:51 AM |  MRS TRIDENTZ
 6 | 2020/07/28 08:33:14 AM |  MRS TRIDENTZ
 7 | 2020/07/28 08:25:06 AM |  MRS S F D SHERILA
 8 | 2020/07/28 08:11:35 AM |  MRS S F D SHERILA

I use this query below.

select to_char(l.txn_date,'YYYY/MM/DD HH12:MI:SS AM') "DATE",l.bname
from linv l where (bname) IN (select bname from linv
where txn_date like '28-JUL-20%'
group by bname, txn_date
having count(*) > 1) and l.txn_type = 'OPD' and payment_mode = 'CREDIT' 
and inv_num like 'AGL%' 
and txn_date like '28-JUL-20%'
order by l.txn_date desc, l.bname

But this does not give me the desired output as some of the required rows are not fetching.

I tried to change group by to to_char(txn_date, 'HH24') but that did not work too.

I think the group_by txn_date has the issue but couldn't get it fixed. Can some one please help?

Thanks!

What results do you want?

Gordon Linoff
– Gordon Linoff

2020-07-30 10:35:09 +00:00
Commented Jul 30, 2020 at 10:35 — Gordon Linoff
– Gordon Linoff, Commented Jul 30, 2020 at 10:35
I made some edits and specified the results I want

Claude
– Claude

2020-08-05 12:10:53 +00:00
Commented Aug 5, 2020 at 12:10 — Claude
– Claude, Commented Aug 5, 2020 at 12:10

K4M · Accepted Answer · 2020-07-29 13:25:17Z

2

If you're trying to group based on datetime up-to the minute, you can use TRUNC function to get rid of seconds & milliseconds.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions209.htm#SQLRF06151

Use this in your group by instead

group by bname, to_char(TRUNC(txn_date, 'MI'), 'DD-MON-YYYY HH24:MI:SS')

answered Jul 29, 2020 at 13:25

K4M

9624 silver badges16 bronze badges

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

2 Comments

Claude Over a year ago

Thanks! I have a concern, suppose there are 3 rows with bname as X and txn_date as date1-time1. Another row with bname as X and txn_date as date1-time2. Is it possible for all 4 rows to fetch? I am experiencing that

K4M Over a year ago

if time1 and time2 are same up-to (excluding) seconds, then yes. That's the purpose of TRUNC(.., 'MI)) to truncate all details (seconds, milliseconds) after the minute.

Gordon Linoff · Accepted Answer · 2020-07-29 12:06:30Z

1

Does exists do what you want?

select l.*
from linv l
where exists (select 1
              from linv l2
              where trunc(l2.txn_date) = trunc(l.txn_date) and
                    l2.bname = l.bname
             );

You can add additional filters as well. Your query suggests:

with l as (
      select l.*
      from linv l
      where l.txn_type = 'OPD' and
            l.payment_mode = 'CREDIT' and
            l.inv_num like 'AGL%' 
            l.txn_date >= date '2020-07-28' and
            l.txn_date < date '2020-07-29' 
           )
select l.*
from l l
where exists (select 1
              from l l2
              where trunc(l2.txn_date) = trunc(l.txn_date) and
                    l2.bname = l.bname
             );

Or using window functions:

with l as (
      select l.*
      from linv l
      where l.txn_type = 'OPD' and
            l.payment_mode = 'CREDIT' and
            l.inv_num like 'AGL%' 
            l.txn_date >= date '2020-07-28' and
            l.txn_date < date '2020-07-29' 
     )
select l.*
from (select l.*, count(*) over (partition by trunc(txn_date), bname) as cnt
      from l
     )l
where cnt >= 2;

answered Jul 29, 2020 at 12:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Claude Over a year ago

Thanks Sir, I want to give exists a try and used the 2nd example but it fetches all rows for where clauses

Collectives™ on Stack Overflow

Oracle - Select rows Group By based on multiple columns

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related