Forum Stats

  • 3,769,900 Users
  • 2,253,029 Discussions
  • 7,875,233 Comments

Discussions

listagg based on a column

User_U66J8
User_U66J8 Member Posts: 89 Red Ribbon

Hi All,


Let us consider the below in Oracle DB 19c EE:

with t as (select 'AAAA' as earnings_row

         ,1000 as person_id

         ,2000 as assignment_id

         ,100 as earnings_amount

         ,1 as rn

      from dual 

       

      union all 

       

      select 'BBB' as earnings_row

         ,1000 as person_id

         ,2000 as assignment_id

         ,200 as earnings_amount

         ,2 as rn

      from dual

       

      union all 

       

      select 'CCC' as earnings_row

         ,1000 as person_id

         ,3000 as assignment_id

         ,300 as earnings_amount

         ,3 as rn

      from dual

      )



Now... I would like to produce always one line but based on assignment_id I need to have a separator with the keyword NEXT.


So.. If there assigment_id is same( value of 2000 for all 3 lines) then the final output should be :


AAAA,BBB,CCC#TOTAL#,"600",#END#,"600



The above result shows all the earnings_row listagg with comma, the total for the assignment and total for the person.


But , with the given example, I want to achieve the below result:


AAAA,BBB#TOTAL#,"300",#NEXT#,CCC#TOTAL#,"300",#END#,"600"


with the scenario with only one assignment_id I did it like the below:

select  listagg(earnings_row, ',') within group (order by rn) over(partition by assignment_id) || 

'#TOTAL#,"' || round(sum(earnings_amount) over(partition by assignment_id order by assignment_id),2) || '",#END#,"'|| 

round(sum(earnings_amount) over(partition by person_id order by person_id),2) from t




Any ideas when the assignment_id is different?


Thanks in advance,

Aleks

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond
    Accepted Answer

    Hi, @User_U66J8

    Thanks for posting the sample data in a usable form; that's very helpful!

    So you already know how to get the correct results for a single assignment_id. If you use the aggregate LISTAGG function instead of the analytic version, like this:

    SELECT  LISTAGG (earnings_row, ',')
                WITHIN GROUP (ORDER BY earnings_row)
         || '#TOTAL#,"'
         || ROUND (SUM (earnings_amount), 2)
         || '"' AS assignment_id_str
    FROM   t
    GROUP BY assignment_id
    ;
    

    that would get results like this:

    ASSIGNMENT_ID_STR
    --------------------------------------------------------------------------------
    AAAA,BBB#TOTAL#,"300"
    CCC#TOTAL#,"300"
    

    Now, you don't want a separate row for each assignment_id; you want to concatenate all these sepoarate rows together. You can call LISTAGG again to do that:


    WITH  one_row_per_assignment_id  AS
    (
    	SELECT   assignment_id
    	,	 SUM (earnings_amount) AS subtotal_earnings_amount -- or ROUND (...)
    	,	 LISTAGG (earnings_row, ',')
    		     WITHIN GROUP (ORDER BY earnings_row)
                 || '#TOTAL#,"'
        	     || ROUND (SUM (earnings_amount), 2)
        	     || '"' 	   		 AS assignment_id_str
            FROM     t
    	GROUP BY assignment_id
    )
    SELECT  LISTAGG (assignment_id_str, ',#NEXT#.')
    	   WITHIN GROUP (ORDER BY assignment_id) -- or whatever you want
         || ',#END#,"'
         || ROUND (SUM (subtotal_earnings_amount), 2)
         || '"' AS output_str
    FROM	 one_row_per_assignment_id
    ;
    

    Output:

    OUTPUT_STR
    --------------------------------------------------------------------------------
    AAAA,BBB#TOTAL#,"300",#NEXT#.CCC#TOTAL#,"300",#END#,"600"
    


Answers

  • mathguy
    mathguy Member Posts: 10,163 Blue Diamond

    First, if you need to aggregate (get a single row of output), you should be using the aggregate version of LISTAGG (and of SUM, etc.) - not the analytic versions. You said if the assignment id is the same (2000 for all input rows) then you get the desired result. You don't - you get the same result THREE TIMES, because you used analytic functions instead of aggregates.

    So - remove the analytic clauses, and instead GROUP BY assignment_id.

    Second, what you have so far (after you change it as explained above) is fine - it's the needed first step. The remaining step is to use LISTAGG again, on the result set, using #NEXT# as the delimiter.

    Instead of posting a complete solution, I'll let you try - write back if you need more help. Good luck!

    User_U66J8
  • mathguy
    mathguy Member Posts: 10,163 Blue Diamond
    edited Oct 27, 2021 1:32PM

    Oh - I just noticed that you are summing something partitioned over person_id, not over assignment_id. Is that what you need? Explain the role of person_id in your problem; do you need to "group" by assignment id, then by person id, and only then (third stage) over all persons? Or do you, in fact, need one row PER PERSON in the output, and the #NEXT# aggregation is only for different assignments for the same person? If different persons may have the same assignment id (on some rows), your requirement is nonsensical.

    As an aside, if in an analytic clause you order by the same thing that you are also partitioning by, the ORDER BY is superfluous; in a partition by some column xyz, if you also order by xyz you are, in fact, not ordering in any way (since xyz is the same for all rows in a partition). For SUM, you might as well leave ORDER BY out altogether. For functions that require an ORDER BY clause (such as ROW_NUMBER) you may write ORDER BY NULL.

    User_U66J8
  • User_H3J7U
    User_H3J7U Member Posts: 688 Silver Trophy
    with t(earnings_row,assignment_id,earnings_amount,rn) as (
     select 'AAA',2000,2,1 from dual union all 
     select 'BBB',2000,20,2 from dual union all 
     select 'CCC',1000,1,3 from dual union all 
     select 'DDD',2000,200,4 from dual)
    select listagg(
     listagg(earnings_row, ',') within group (order by rn)
     ||'#TOTAL#,"'||sum(earnings_amount), '",#NEXT#,') within group (order by assignment_id)
     ||'",#END#,"'||sum(sum(earnings_amount))||'"' str
    from t group by assignment_id;
    
    STR                                                      
    ----------------------------------------------------------
    CCC#TOTAL#,"1",#NEXT#,AAA,BBB,DDD#TOTAL#,"222",#END#,"223"
    


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond
    Accepted Answer

    Hi, @User_U66J8

    Thanks for posting the sample data in a usable form; that's very helpful!

    So you already know how to get the correct results for a single assignment_id. If you use the aggregate LISTAGG function instead of the analytic version, like this:

    SELECT  LISTAGG (earnings_row, ',')
                WITHIN GROUP (ORDER BY earnings_row)
         || '#TOTAL#,"'
         || ROUND (SUM (earnings_amount), 2)
         || '"' AS assignment_id_str
    FROM   t
    GROUP BY assignment_id
    ;
    

    that would get results like this:

    ASSIGNMENT_ID_STR
    --------------------------------------------------------------------------------
    AAAA,BBB#TOTAL#,"300"
    CCC#TOTAL#,"300"
    

    Now, you don't want a separate row for each assignment_id; you want to concatenate all these sepoarate rows together. You can call LISTAGG again to do that:


    WITH  one_row_per_assignment_id  AS
    (
    	SELECT   assignment_id
    	,	 SUM (earnings_amount) AS subtotal_earnings_amount -- or ROUND (...)
    	,	 LISTAGG (earnings_row, ',')
    		     WITHIN GROUP (ORDER BY earnings_row)
                 || '#TOTAL#,"'
        	     || ROUND (SUM (earnings_amount), 2)
        	     || '"' 	   		 AS assignment_id_str
            FROM     t
    	GROUP BY assignment_id
    )
    SELECT  LISTAGG (assignment_id_str, ',#NEXT#.')
    	   WITHIN GROUP (ORDER BY assignment_id) -- or whatever you want
         || ',#END#,"'
         || ROUND (SUM (subtotal_earnings_amount), 2)
         || '"' AS output_str
    FROM	 one_row_per_assignment_id
    ;
    

    Output:

    OUTPUT_STR
    --------------------------------------------------------------------------------
    AAAA,BBB#TOTAL#,"300",#NEXT#.CCC#TOTAL#,"300",#END#,"600"
    


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond

    Hi,

    Instead of using a sub-query, you could nest the aggregate functions, like this:

    SELECT   LISTAGG ( LISTAGG (earnings_row, ',')
    		    WITHIN GROUP (ORDER BY earnings_row)
                      || '#TOTAL#,"'
        	          || ROUND (SUM (earnings_amount), 2)
        	    	  || '"'
    	 	 , ',#NEXT#.'
    		 ) WITHIN GROUP (ORDER BY assignment_id) -- or whatever you want
        || ',#END#,"'
        || ROUND (SUM (SUM (earnings_amount)), 2)
        || '"' AS output_str
    FROM	 t
    GROUP BY assignment_id
    ;
    

    but I would still use a sub-query. I find it easier to read and understand, and therefore easier to debug and maintain.