CODEWITHSUNDEEP
sqloracle-databaseoracle11g
simplify_life
simplify_life

Reputation: 405

Oracle sql group sum

I have table With ID,Sub_ID and value coloumns 

ID  SUB_ID  Value
100 1   100
100 2   150
101 1   100
101 2   150
101 3   200
102 1   100

SUB ID can vary from 1..maxvalue( In this example it is 3). I need Sum of values for each Sub_ID. If SUB_ID is less than MAXVALUE for a particlaur ID then it should take MAX(SUB_ID) of each ID As shown below ( In this example for ID=100 for SUB_ID 3 it should take 150 i.e 2<3 so value=150))

SUB_ID  SUM(values) Remarks
1           300         (100+100+100)
2           400         (150+150+100)
3           450         (150+200+100)

This can be easily done in PL/SQL . Can we use SQL for the same using Model Clause or any other options

Upvotes: 1

Views: 1282

Answers (3)

MT0
MT0

Reputation: 167822

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TableA ( ID, SUB_ID, Value ) AS
          SELECT 100, 1, 100 FROM DUAL
UNION ALL SELECT 100, 2, 150 FROM DUAL
UNION ALL SELECT 101, 1, 100 FROM DUAL
UNION ALL SELECT 101, 2, 150 FROM DUAL
UNION ALL SELECT 101, 3, 200 FROM DUAL
UNION ALL SELECT 102, 1, 100 FROM DUAL

Query 1:

WITH sub_ids AS (
  SELECT LEVEL AS sub_id
  FROM   DUAL
  CONNECT BY LEVEL <= ( SELECT MAX( SUB_ID ) FROM TableA )
),
max_values AS (
  SELECT ID,
         MAX( VALUE ) AS max_value
  FROM   TableA
  GROUP BY ID
)
SELECT s.SUB_ID,
       SUM( COALESCE( a.VALUE, m.max_value ) ) AS total_value
FROM   sub_ids s
       CROSS JOIN
       max_values m
       LEFT OUTER JOIN
       TableA a
       ON ( s.SUB_ID = a.SUB_ID AND m.ID = a.ID )
GROUP BY
       s.SUB_ID

Results:

| SUB_ID | TOTAL_VALUE |
|--------|-------------|
|      1 |         300 |
|      2 |         400 |
|      3 |         450 |

Upvotes: 2

Mark Leiber
Mark Leiber

Reputation: 3138

How about something like this:

select max_vals.sub_id, sum(nvl(table_vals.value,max_vals.max_value)) as sum_values
from (
    select all_subs.sub_id, t1.id, max(t1.value) as max_value
    from your_table t1
    cross join (select sub_id from your_table) all_subs
    group by all_subs.sub_id, t1.id
) max_vals
left outer join your_table table_vals
on max_vals.id = table_vals.id
and max_vals.sub_id = table_vals.sub_id
group by max_vals.sub_id;

The inner query gets you a list of all sub_id/id combinations and their fall-back values. The out query uses an nvl to use the table value if it exists and the fall-back value if it doesn't.

Upvotes: 0

Raj Kamuni
Raj Kamuni

Reputation: 388

Try this

SELECT SUB_ID,SUM(values),
 (SELECT DISTINCT SUBSTRING(   
(   
SELECT '+'+ CAST(values AS VARCHAR)
  FROM table_Name  AS T2  
WHERE T2.SUB_ID = d.SUB_ID        
 FOR XML PATH ('') 
),2,100000)[values]) as values  
FROm table_Name d
GROUP BY SUB_ID

Upvotes: 0

Related Questions