CODEWITHSUNDEEP
sqloracle-databasegroup-bysumaggregate
wast
wast

Reputation: 996

Oracle SQL Conditional SUM

I have a table of names and numeric values and I want to sum values grouped by names. This part is simple:

SELECT
        name,
        SUM(my_value) "MyValue"
    FROM my_table     
    GROUP BY name

But I also have string 'UNLIMITED' in values. When there is 'UNLIMITED' in the group, I just want to select value 'UNLIMITED' and not do any sum. This is what I came up with using UNION but I know there is a better way:

SELECT 
name, 
MAX("MyValue") 
FROM (
    SELECT
        name,
        'UNLIMITED' "MyValue"
    FROM my_table
    WHERE my_value = 'UNLIMITED' 
    GROUP BY name
    UNION
    SELECT
        name,
        TO_CHAR(SUM(
            CASE WHEN my_value = 'UNLIMITED'
            THEN '0'
            ELSE my_value END
        )) "MyValue"
    FROM my_table     
    GROUP BY name
) t 
GROUP BY name

Please check SqlFiddle for real example.

Example table

NAME    MY_VALUE
name1   50
name1   20
name2   30
name2   UNLIMITED

Example wanted result

NAME    SUM("MYVALUE")
name1   70
name2   UNLIMITED

Upvotes: 3

Views: 1991

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is a pretty easy way to express the logic:

SELECT name,
       (CASE WHEN MAX(my_value) = 'UNLIMITED' THEN 'UNLIMITED'
             ELSE TO_CHAR(SUM(CASE WHEN my_value <> 'UNLIMITED' THEN my_value END))
        END)
FROM my_table
GROUP BY name;

This uses the fact that characters are ordered after numbers.

Or similar logic:

SELECT name,
       (CASE WHEN COUNT(*) <> COUNT(NULLIF(my_value, 'UNLIMITED')) THEN 'UNLIMITED'
             ELSE TO_CHAR(SUM(NULLIF(my_value, 'UNLIMITED')))
        END)
FROM my_table
GROUP BY name;

Upvotes: 3

T.S.
T.S.

Reputation: 19330

This works

create  table tempxx (a nvarchar2(10), b nvarchar2(20))
insert into tempxx values ('a', 50);
insert into tempxx values ('a', 20);
insert into tempxx values ('b', 30);
insert into tempxx values ('b', 'UNLIMITED');


SELECT allt.a, decode(ut.max, NULL, to_char(allt.sum), to_char(ut.max)) as val
From
((SELECT
    a,
    sum(decode(b, 'UNLIMITED', 0, b)) sum
FROM tempxx 
Group by a) allT left join 
(SELECT
    a,
   Max(b) max
FROM tempxx 
WHERE  b = 'UNLIMITED'  
Group by a) ut on allt.a = ut.a)

A   VAL
------------
b   UNLIMITED
a   70

Basically, select all rows on left and join with only unlimited. If unlimited record is null, keep left one, otherwise take unlimited data

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

One way to do it with window functions.

SELECT DISTINCT
    name,        
    CASE WHEN sum(case when my_value = 'UNLIMITED' then 1 else 0 end) over(partition by name) >= 1
    THEN 'UNLIMITED'
    ELSE cast(sum(case when my_value = 'UNLIMITED' then 0 else cast(my_value as number) end) over(partition by name)
              as varchar(255))
    end as myval
FROM my_table

Upvotes: 2

Related Questions