How to write sql query to generate a group no for each grouped record

Question

following is scenario:

I have data in following format:

    entryid ,          ac_no,   db/cr,     amt
    -----------------------------------------------
    1                   10      D      5   
    1                   11      C      5         
    2                   01      D      8                            
    2                   11      C      8         
    3                   12      D      10         
    3                   13      C      10         
    4                   14      D      5         
    4                   16      C      5         
    5                   14      D      2           
    5                   17      C      2           
    6                   14      D      3           
    6                   18      C      3

I want data in following format:

So far i have acheived the first 3 columns by query

select wm_concat(entryid),ac_no,db_cr,Sum(amt) from t1 group by ac_no,db_cr

    wm_Concat(entryid),ac_no, db/cr, Sum(amt),  set_id
    ------------------------------------------------
    1                   10    D      5           S1 
    2                   01    D      8           S1
    1,2                 11    C      13          S1
    3                   12    D      10          S2
    3                   13    C      10          S2
    4,5,6               14    D      10          S3 
    4                   16    C      5           S3
    5                   17    C      2           S3
    6                   18    C      3           S3


I want an additional column `set_id` that either shows this S1, S2.. or any number 1,2.. so that the debit & credit entries sets can be identified.

I am making sets of debit and credit entries based on their Ac_no values. Any little help will be highly appreciated. Thanks

I don't understand the motive here. What is the primary key here and why you want to group them? — Ubercool, Dec 14 '16 at 05:51
@Pramod motive is, while showing the result to user in gui along with checkboxes, if user wants to deselect a debit entry then corresponding amount can be deducted from the summed credit entry of that set. there is no primary key because the output is a result of a query, however we can use row_number.. for numbering records. — Ratnesh, Dec 14 '16 at 05:55
I don't think those results make sense given the query - why have you got two rows for ac_no = 0000 and db/cr = D? Surely you should end up with only one? Same for (1256, D). Also, why are you using wm_concat, which is an undocumented and unsupported function. If you're on 11g or above, you should be using LISTAGG — Boneist, Dec 14 '16 at 09:26

score 0 · Answer 1 · answered Dec 14 '16 at 06:07

Create a new column say set and give a unique identifier to the particular set. So for example the first three records will have set id S1, next two will have S2 and so on.

To distinguish a transaction from a set you can use column db/cr along with newly added set column. You can identify that the 3rd row is a set since it's transaction type is 'C' whereas the transactions are of type 'D'.

Here I have assumed that your transactions are debit only, if not please provide more details in the question. Hope this helps.

How to write sql query to generate a group no for each grouped record

1 Answers1