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

# SQL to sum a column while avoiding repeating values

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.

[P.S. : how can I write a post with monospace characters??]
• ###### 1. Re: SQL to sum a column while avoiding repeating values
``````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
• ###### 2. Re: SQL to sum a column while avoiding repeating values
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``````
``````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
user3292382 wrote:
[P.S. : how can I write a post with monospace characters??]
``````{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
Thank you everybody!

Regards