Home » SQL & PL/SQL » SQL & PL/SQL » Group result set in a count of 5 records.
Group result set in a count of 5 records. [message #683800] Mon, 22 February 2021 12:00 Go to next message
Heavyd
Messages: 7
Registered: February 2021
Junior Member
I would like to group the result set in a count of 5 records.

Table Customers
CustomerId
123
123
123
123
123
123
123

CustomerId 123 occurs 7 times in the table. But I want a CustomerId group by 5 records.

Result of query
CustomerId number of records
123 5
123 2


Who can help me out? I tried several group by queries but I can't get the correct result.
It seems easy but I can't figure it out Sad
Re: Group result set in a count of 5 records. [message #683801 is a reply to message #683800] Mon, 22 February 2021 12:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3045
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3045
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.
Re: Group result set in a count of 5 records. [message #683806 is a reply to message #683804] Mon, 22 February 2021 13:31 Go to previous messageGo to next message
Heavyd
Messages: 7
Registered: February 2021
Junior Member
That's amazing!
I never heard of match_recognize. I was all day busy with grouping and analytical functions but nothing worked.

If want to group by 20 I just change PATTERN(C{,5}) to PATTERN(C{,20}) ?
And for grouping on more columns I add it at PARTITION BY CUSTID, TRANSACTIONDATE?

Thank you very much.
Re: Group result set in a count of 5 records. [message #683807 is a reply to message #683804] Mon, 22 February 2021 13:53 Go to previous messageGo to next message
Heavyd
Messages: 7
Registered: February 2021
Junior Member
Solomon Yakobson wrote on Mon, 22 February 2021 13:11
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.
Would a having sum(amount) > 0 be possible in that query? If the customers table had an amount column.
Re: Group result set in a count of 5 records. [message #683809 is a reply to message #683807] Mon, 22 February 2021 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
If the customers table had an amount column.

So post a test case with this column and the result you want for the data you give.

Re: Group result set in a count of 5 records. [message #683884 is a reply to message #683809] Tue, 02 March 2021 08:12 Go to previous message
Heavyd
Messages: 7
Registered: February 2021
Junior Member
The solution described was enough. Thanks for the help.
Previous Topic: Comparing input values with table data
Next Topic: Reading text from binary data in a blob
Goto Forum:
  


Current Time: Tue Apr 13 17:35:44 CDT 2021