This discussion is archived
4 Replies Latest reply: Jan 31, 2013 6:25 AM by user3292382 RSS

SQL to sum a column while avoiding repeating values

user3292382 Newbie
Currently Being Moderated
Hello,

I have these records in a table:

CODE AMOUNT DESCRIPTION
AAA 5 five dollars for pizza
AAA 2 two dollars for tips
AAA 1 one dollar for dogsitting
BBB 6 six dollars for babysitting
BBB 1 one dollar for tips

My goal is to list all records, "grouping" by code, with sum(amount), but the final display has to show all descriptions, one for row, avoiding to repeat the "CODE" column and "sum(AMOUNT)" column.
The result should be like this:

CODE SUM(AMOUNT) DESCRIPTION
AAA 8 five dollars for pizza
two dollars for tips
one dollar for dogsitting
BBB 7 six dollars for babysitting
one dollar for tips

That is, the "CODE" is displayed only the first row, with its sum of "amount".
I think I have to use the analytics functions, but I was a little stuck.

Thank you in advance.

[Click here to see a better image of what I want|http://www.cinemapioxi.it/test/tbl.jpg]

[P.S. : how can I write a post with monospace characters??]
  • 1. Re: SQL to sum a column while avoiding repeating values
    jeneesh Guru
    Currently Being Moderated
    with sample_data as
    (
    select 'AAA' code, 5 amount, 'five dollars for pizza' description from dual union all
    select 'AAA', 2, 'two dollars for tips' from dual union all
    select 'AAA', 1, 'one dollar for dogsitting' from dual union all
    select 'BBB', 6, 'six dollars for babysitting' from dual union all
    select 'BBB', 1, 'one dollar for tips' from dual
    ),
    data_with_rn as
    (
    select code,amount,description,
          row_number() over(partition by code order by amount desc) rn,
          sum(amount) over(partition by code order by null) sm
    from sample_data
    )
    select decode(rn,1,code) code_value,decode(rn,1,sm) sum_amount,description
    from data_with_rn
    order by code,rn;
    
    CODE_VALUE SUM_AMOUNT DESCRIPTION               
    ---------- ---------- ---------------------------
    AAA                 8 five dollars for pizza      
                          two dollars for tips        
                          one dollar for dogsitting   
    BBB                 7 six dollars for babysitting 
                          one dollar for tips 
    Edited by: jeneesh on Jan 31, 2013 3:21 PM
    Added OREDR BY
  • 2. Re: SQL to sum a column while avoiding repeating values
    Manik Expert
    Currently Being Moderated
    Check this based on your data.....
    WITH t AS
            (SELECT 'AAA' code, 5 amount, 'five dollars for pizza' description
               FROM DUAL
             UNION ALL
             SELECT 'AAA', 2, 'two dollars for tips' FROM DUAL
             UNION ALL
             SELECT 'AAA', 1, 'one dollar for dogsitting' FROM DUAL
             UNION ALL
             SELECT 'BBB', 6, 'six dollars for babysitting' FROM DUAL
             UNION ALL
             SELECT 'BBB', 1, 'one dollar for tips' FROM DUAL)
             SELECT CASE
                       WHEN code =
                               LAG (code, 1, 'DDDDD')
                                  OVER (PARTITION BY code ORDER BY code) THEN
                          NULL
                       ELSE
                          code
                    END
                       code,
                    SUM (amount) OVER (PARTITION BY code) sum_amt,
                    description
               FROM t;
    output:
    CODE     SUM_AMT     DESCRIPTION
    AAA     8     five dollars for pizza
         8     two dollars for tips
         8     one dollar for dogsitting
    BBB     7     six dollars for babysitting
         7     one dollar for tips
    Overlooked your problem:
    WITH t AS
            (SELECT 'AAA' code, 5 amount, 'five dollars for pizza' description
               FROM DUAL
             UNION ALL
             SELECT 'AAA', 2, 'two dollars for tips' FROM DUAL
             UNION ALL
             SELECT 'AAA', 1, 'one dollar for dogsitting' FROM DUAL
             UNION ALL
             SELECT 'BBB', 6, 'six dollars for babysitting' FROM DUAL
             UNION ALL
             SELECT 'BBB', 1, 'one dollar for tips' FROM DUAL),
         t1 AS
            (SELECT CASE
                       WHEN code =
                               LAG (code, 1, 'DDDDD')
                                  OVER (PARTITION BY code ORDER BY code) THEN
                          NULL
                       ELSE
                          code
                    END
                       code,
                    SUM (amount) OVER (PARTITION BY code) sum_amt,
                    description
               FROM t)
    SELECT code, CASE WHEN code IS NULL THEN NULL ELSE sum_amt END sum_amt, description
      FROM t1;
    output:
    CODE     SUM_AMT     DESCRIPTION
    AAA     8     five dollars for pizza
              two dollars for tips
              one dollar for dogsitting
    BBB     7     six dollars for babysitting
              one dollar for tips
    Cheers,
    Manik.

    Edited by: Manik on Jan 31, 2013 3:34 PM
  • 3. Re: SQL to sum a column while avoiding repeating values
    jeneesh Guru
    Currently Being Moderated
    user3292382 wrote:
    [P.S. : how can I write a post with monospace characters??]
    Include your SQL/Data in {noformat}
    {noformat} tags..
    
    Like
    {noformat}
    {noformat}
    select *
    from dual
    {noformat}
    {noformat}
    
    The above will be displayed as
    select *
    from dual
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 4. Re: SQL to sum a column while avoiding repeating values
    user3292382 Newbie
    Currently Being Moderated
    Thank you everybody!

    Regards

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points