Hello I am very new to SQL and I have an issue. I have 3 tables job, revenue and cost. job.unid = cost.job_unid and revenue.job_unid
I want to find the total revenue and cost for each job and then find the profit for each job all on one row:
The headers looking something like this: Job No, Total Revenue, Total Cost, P&L (Rev-Cost)
I tried to make a single query, but after searching on the internet for solutions it seemed like i should query Revenue and Cost seperately and then combine using Union All.
Now I am doing this:
x
select m,
rlc,
rvatlc,
rtotlc,
clc,
cvatlc,
ctotlc
from(
(select decode(j.consolno,null,j.shpno,j.consolno) m,
sum(nvl(r.amtlc,0)) rlc,
sum(nvl(r.vatamtlc,0)) rvatlc,
sum(nvl(r.amtlc,0))+sum(nvl(r.vatamtlc,0)) rtotlc,
0 clc,
0 cvatlc,
0 ctotlc
from
job j
full join revenue r on j.unid=r.job_unid
where j.voidby is null and decode(j.consolno,null,j.shpno,j.consolno)='SHSEM00131'
group by decode(j.consolno,null,j.shpno,j.consolno)
)
union all
(select decode(j.consolno,null,j.shpno,j.consolno) m,
0 rlc,
0 rvatlc,
0 rtotlc,
sum(nvl(c.amtlc,0)) clc,
sum(nvl(c.vatamtlc,0)) cvatlc,
sum(nvl(c.amtlc,0))+sum(nvl(c.vatamtlc,0)) ctotlc
from
job j, cost c
where j.voidby is null and j.unid=c.job_unid and decode(j.consolno,null,j.shpno,j.consolno)='SHSEM00131'
group by decode(j.consolno,null,j.shpno,j.consolno)
))
The result is:
I am not sure if you can see but it is 2 lines:
1 is the revenue and 1 is the cost.
I want to combine these 2 lines
Advertisement
Answer
Try this.
select m,
sum(rlc),
sum(rvatlc),
sum(rtotlc),
sum(clc),
sum(cvatlc),
sum(ctotlc)
from(
(select decode(j.consolno,null,j.shpno,j.consolno) m,
sum(nvl(r.amtlc,0)) rlc,
sum(nvl(r.vatamtlc,0)) rvatlc,
sum(nvl(r.amtlc,0))+sum(nvl(r.vatamtlc,0)) rtotlc,
0 clc,
0 cvatlc,
0 ctotlc
from
job j
full join revenue r on j.unid=r.job_unid
where j.voidby is null and decode(j.consolno,null,j.shpno,j.consolno)='SHSEM00131'
group by decode(j.consolno,null,j.shpno,j.consolno)
)
union all
(select decode(j.consolno,null,j.shpno,j.consolno) m,
0 rlc,
0 rvatlc,
0 rtotlc,
sum(nvl(c.amtlc,0)) clc,
sum(nvl(c.vatamtlc,0)) cvatlc,
sum(nvl(c.amtlc,0))+sum(nvl(c.vatamtlc,0)) ctotlc
from
job j, cost c
where j.voidby is null and j.unid=c.job_unid and decode(j.consolno,null,j.shpno,j.consolno)='SHSEM00131'
group by decode(j.consolno,null,j.shpno,j.consolno)
)) group by m;