4 Replies Latest reply: Jan 31, 2013 8:25 AM by user3292382 RSS

    SQL to sum a column while avoiding repeating values

    user3292382
      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
          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
            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
              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
                Thank you everybody!

                Regards