PLSQL Dynamic Row Count in results

Question

In PL SQL is there a way to produce the Order Count per customer as follows... Thanks for your help.

Cust     Order#    Order Count
ABC1     011       1 
ABC1     052       2
ABC1     199       3
BBA1     150       1
BBA1     158       2

Thanks Gavin

Please edit your question and add the code of your stored procedure that you want to change. Especially the part that does the retrieval — user330315
– user330315, Commented Aug 23, 2018 at 19:19

Littlefoot · Accepted Answer · 2018-08-23 20:24:39Z

1

If I understood you correctly, a little bit of analytics might do the job. Here's an example:

SQL> with test (cust, order#) as
  2    (select 'ABC1', '011' from dual union all
  3     select 'ABC1', '052' from dual union all
  4     select 'ABC1', '199' from dual union all
  5     select 'BBA1', '150' from dual union all
  6     select 'BBA1', '158' from dual
  7    )
  8  select cust, order#,
  9    row_number() over (partition by cust order by order#) order_count
 10  from test;

CUST ORD ORDER_COUNT
---- --- -----------
ABC1 011           1
ABC1 052           2
ABC1 199           3
BBA1 150           1
BBA1 158           2

SQL>

answered Aug 23, 2018 at 20:24

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

1 Comment

Gavin Over a year ago

This was perfect thank you, and thank you to all those who helped me, really appreciated.

IT Alex · Accepted Answer · 2018-08-23 19:21:16Z

0

sounds like you want a GROUP BY such as

select cust, SUM(order_count)
from MyTable
group by cust;

which should yield

cust   SUM
ABC1   6
BBA1   3

answered Aug 23, 2018 at 19:21

IT Alex

2003 silver badges12 bronze badges

Collectives™ on Stack Overflow

PLSQL Dynamic Row Count in results

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related