CODEWITHSUNDEEP
sqloracle-databaseplsqldeveloper
surendar
surendar

Reputation: 9

oracle query to get the sum of column for different values

I'm trying to get the sum of particular column

I want oracle query to get the sum of column "timespend" for each scenarios in (S_1, S_4, S_5, S_8) where name is equals to B

name id scenario timespend
A   123  S_1     2
A   123  S_1     5
A   123  S_3    6.3
B   124  S_1    3
B   124  S_1    8.9
B   124  S_1    5
B   124  S_1    4
B   124  S_5    1.23
B   124  S_5    56
B   124  S_5    8
B   124  S_8    9
B   124  S_8    4
C   125  S_8    6
D   126  S_2    9
D   126  s_4    5
D   126  s_4    6.2
D   126  s_4    7
E   127  S_1    8
E   127  S_1    1

I have used below query but its returning with sum of column timespend

select sum(timespend),
       scenario
from   table1
where  scenario in ('s_1','s_3','s_4','s_5','s_8')
and    name = 'B'
group by scenario

output of the query is

sum(timespend) scenario
-------------- --------
154.63          s_1
154.63          s_5
154.63          s_8

Expected output is

sum(timespend) scenario
-------------- --------
20.9            s_1
65.23           s_5
19              s_8

Can anyone help me to get the expected output as like mentioned above?

Upvotes: 0

Views: 1250

Answers (1)

Boneist
Boneist

Reputation: 23578

I don't see an issue with your query (although your expected output for scenario S_8 is wrong, plus you used lower case s in your where clause):

with table1 as (select 'A' name, 123 id, 'S_1' scenario, 2 timespend from dual union all
                select 'A' name, 123 id, 'S_1' scenario, 5 timespend from dual union all
                select 'A' name, 123 id, 'S_3' scenario, 6.3 timespend from dual union all
                select 'B' name, 124 id, 'S_1' scenario, 3 timespend from dual union all
                select 'B' name, 124 id, 'S_1' scenario, 8.9 timespend from dual union all
                select 'B' name, 124 id, 'S_1' scenario, 5 timespend from dual union all
                select 'B' name, 124 id, 'S_1' scenario, 4 timespend from dual union all
                select 'B' name, 124 id, 'S_5' scenario, 1.23 timespend from dual union all
                select 'B' name, 124 id, 'S_5' scenario, 56 timespend from dual union all
                select 'B' name, 124 id, 'S_5' scenario, 8 timespend from dual union all
                select 'B' name, 124 id, 'S_8' scenario, 9 timespend from dual union all
                select 'B' name, 124 id, 'S_8' scenario, 4 timespend from dual union all
                select 'C' name, 125 id, 'S_8' scenario, 6 timespend from dual union all
                select 'D' name, 126 id, 'S_2' scenario, 9 timespend from dual union all
                select 'D' name, 126 id, 'S_4' scenario, 5 timespend from dual union all
                select 'D' name, 126 id, 'S_4' scenario, 6.2 timespend from dual union all
                select 'D' name, 126 id, 'S_4' scenario, 7 timespend from dual union all
                select 'E' name, 127 id, 'S_1' scenario, 8 timespend from dual union all
                select 'E' name, 127 id, 'S_1' scenario, 1 from dual)
-- end of mimicking your table1 with data in it. See SQL below:
select sum(timespend),
       scenario
from   table1
where  scenario in ('S_1','S_3','S_4','S_5','S_8')
and    name = 'B'
group by scenario
order by scenario;

SUM(TIMESPEND) SCENARIO
-------------- --------
          20.9 S_1     
         65.23 S_5     
            13 S_8

Perhaps, judging from the fact that some of the scenario data in your sample data are in lowercase and you mentioned you were looking for theset of scenarios that look similar, you might want the following instead?

select sum(timespend),
       upper(scenario) scenario
from   table1
where  upper(scenario) in ('S_1','S_3','S_4','S_5','S_8')
and    name = 'B'
group by upper(scenario)
order by upper(scenario);

Upvotes: 2

Related Questions