Home » SQL & PL/SQL » SQL & PL/SQL » Group result set in a count of 5 records.
|
Re: Group result set in a count of 5 records. [message #683801 is a reply to message #683800] |
Mon, 22 February 2021 12:28 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
The test case must be representative of your data and different cases you have to handle.
I think your example is too simplified.
Here's one containing multiple values:
SQL> create table t (val integer);
Table created.
SQL> insert into t select trunc(level)/7 from dual connect by level <= 20
2 /
20 rows created.
SQL> select * from t order by val;
VAL
----------
0
0
0
1
1
1
1
1
1
1
2
2
2
2
2
2
2
3
3
3
20 rows selected.
SQL> with
2 data as (
3 select val, trunc(row_number() over (partition by val order by null)/5) grp
4 from t
5 )
6 select val
7 from data
8 group by val, grp
9 order by val, grp
10 /
VAL
----------
0
1
1
2
2
3
6 rows selected.
[Updated on: Mon, 22 February 2021 12:29] Report message to a moderator
|
|
|
Re: Group result set in a count of 5 records. [message #683802 is a reply to message #683801] |
Mon, 22 February 2021 13:01 |
|
Heavyd
Messages: 7 Registered: February 2021
|
Junior Member |
|
|
Hello thanks for your help.
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
I added some code
create table customers
( custId integer
, invoiceid integer
, transactiondate date
);
insert into customers(custid, invoiceid,transactiondate)
values (123456, 1, trunc(sysdate));
insert into customers(custid, invoiceid,transactiondate)
values (123456, 2, trunc(sysdate));
insert into customers(custid, invoiceid,transactiondate)
values (123456, 3, trunc(sysdate));
insert into customers(custid, invoiceid,transactiondate)
values (123456, 4, trunc(sysdate));
insert into customers(custid, invoiceid,transactiondate)
values (123456, 5, trunc(sysdate));
insert into customers(custid, invoiceid,transactiondate)
values (123456, 6, trunc(sysdate));
insert into customers(custid, invoiceid,transactiondate)
values (123456, 7, trunc(sysdate));
insert into customers(custid, invoiceid,transactiondate)
values (123456, 8, trunc(sysdate+3));
insert into customers(custid, invoiceid,transactiondate)
values (987654, 10, trunc(sysdate));
insert into customers(custid, invoiceid,transactiondate)
values (987654, 11, trunc(sysdate));
select * from customers;
custid invoiceid transactiondate
123456 1 22/02/2021
123456 2 22/02/2021
123456 3 22/02/2021
123456 4 22/02/2021
123456 5 22/02/2021
123456 6 22/02/2021
123456 7 22/02/2021
123456 8 25/02/2021
987654 10 22/02/2021
987654 11 22/02/2021
The goal is to get a result where I can see records grouped by custid, transactiondate and grouped per x number of records.
5 in this example.
Custid 123456 has 7 records with transactiondate 22/02/2021 so I would like to see 2 records for custid 123456 one with a count of 5 and one with a count of 2.
Custid 123456 has 1 record with transactiondate 25/02/2021, so that would be another record with a count of 1.
Custid 987654 has 2 records with transactiondate 22/02/2021, so that would be another record with a count of 2.
the desired result would be
custid transactiondate group records by 5
123456 22/02/2021 5
123456 22/02/2021 2
123456 25/02/2021 1
987654 22/02/2021 2
I'm no native Englisch speaker. I don't know how to tell it better. Sorry.
[Updated on: Mon, 22 February 2021 13:04] Report message to a moderator
|
|
|
Re: Group result set in a count of 5 records. [message #683803 is a reply to message #683800] |
Mon, 22 February 2021 13:05 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
MATCH_RECOGNIZE solution:
SQL> SELECT *
2 FROM CUSTOMER
3 /
CUSTOMER_ID
-----------
123
123
123
123
123
123
123
456
456
456
789
789
789
789
789
789
789
789
789
789
789
789
789
789
789
25 rows selected.
SQL> SELECT CUSTOMER_ID,
2 CNT
3 FROM CUSTOMER
4 MATCH_RECOGNIZE(
5 PARTITION BY CUSTOMER_ID
6 ORDER BY CUSTOMER_ID
7 MEASURES
8 COUNT(*) CNT
9 ONE ROW PER MATCH
10 PATTERN(C{,5})
11 DEFINE C AS 1 = 1
12 )
13 /
CUSTOMER_ID CNT
----------- ----------
123 5
123 2
456 3
789 5
789 5
789 5
6 rows selected.
SQL>
SY.
|
|
|
Re: Group result set in a count of 5 records. [message #683804 is a reply to message #683803] |
Mon, 22 February 2021 13:11 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Or using your sample:
SELECT CUSTID,
TRANSACTIONDATE,
CNT
FROM CUSTOMERS
MATCH_RECOGNIZE(
PARTITION BY CUSTID,
TRANSACTIONDATE
ORDER BY CUSTID
MEASURES
COUNT(*) CNT
ONE ROW PER MATCH
PATTERN(C{,5})
DEFINE C AS 1 = 1
)
/
CUSTID TRANSACTI CNT
---------- --------- ----------
123456 22-FEB-21 5
123456 22-FEB-21 2
123456 25-FEB-21 1
987654 22-FEB-21 2
SQL>
SY.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:49:25 CDT 2024
|