CODEWITHSUNDEEP
sqloracle-databasegroup-by
ali_codex
ali_codex

Reputation: 113

How to calculate oracle column data with group by

I want to calculate different columns from different tables. i am trying to get my required result from my code but the result give me twice same rows of data

i have 3 tables in my oracle database rolls_purchase,rolls_issue,rolls_consumption

CREATE TABLE ROLLS_purchase(
V_ID INT,
ENT_DATE DATE,
ENT_TIME DATE,
QUALITY VARCHAR2(25),
ROLL_QTY INT,
ROLL_SIZE INT);

CREATE TABLE ROLLS_ISSUE(
V_ID INT,
ISSUE_DATE DATE,
ISSUE_TIME DATE,
QUALITY VARCHAR2(25),
ROLL_SIZE INT,
ROLL_QTY INT,
TO_PO VARCHAR2(100)
);

CREATE TABLE ROLLS_consumption(
V_ID INT,
ISSUE_DATE DATE,
ISSUE_TIME DATE,
QUALITY VARCHAR2(25),
ROLL_SIZE INT,
ROLL_QTY INT,
ROLL_USED INT,
LEFT_ROLL INT
);

when i purchase rolls and put data into my rolls_purchase table,i got result correctly,after that i use rolls_issue form for issue roll then my form coding doing well, 3rd step is when a user consumed the roll,if there are some left and returned to me,i need to add them back when i use my code then the result gives me twice same rows like as below. 

select x.quality,x.roll_size,sum(x.roll_qty)as 
"TOTAL_ROLL",NVL(SUM(I.ROLL_QTY),0)
from rolls x, rolls_issue I
where x.quality is not null
group by x.quality,x.roll_size,I.ROLL_QTY,I.QUALITY;

above codes result is

        quality           size  t.roll    used
 FLOUTING 100 GSM         44    150       40
 FLOUTING 100 GSM         44    150      20
 FLOUTING 125 GSM         36    50        40

i need this result ,if issue my rolls then above code calculate my rolls group by and sum them only one row like below

     quality           size  t.roll    used
 FLOUTING 100 GSM         44    150       60
 FLOUTING 125 GSM         36    50        40

Upvotes: 0

Views: 56

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If I understand correctly, group by before joining:

select quality, roll_size, r.total_roll,
       coalesce(ri.total_issue, 0)
from (select r.quality, r.roll_size, sum(r.qty) as total_roll
      from rolls r
      group by r.quality, r.roll_size
     ) left join
     (select ri.quality, ri.roll_size, sum(ri.qty) as total_issue
      from rolls_issue ri
      group by ri.quality, ri.roll_size
     ) ri
     using (quality, roll_size)

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

Use only p.quality,p.roll_size within grouping clause(I just replaced the alias x with p)

Use such a query with join :

select p.quality, p.roll_size,
       sum(p.roll_qty) as "Total Roll", nvl(sum(i.roll_qty), 0) as "Total Used"
  from rolls_purchased p
  join rolls_issue i
    on p.issue_id = i.v_id
 where p.quality is not null
 group by p.quality, p.roll_size

Upvotes: 1

Related Questions