CODEWITHSUNDEEP
sqloracle-databaseoracle11g
saman
saman

Reputation: 41

how can sum 2 select results in oracle

hi i want sum two results from other select

i try some way but i cant find any ways please help me here is my code

SELECT
name1,count1,count2FROM
(
    SELECT
        trade.name AS name1, COUNT(workorders.id) AS count1
    FROM      workorders
    RIGHT JOIN    trade
    ON trade.id = workorders.trade_id 
    AND     workorders.hcworkorderstat_id IN (
            SELECT     hcworkorderstat.id
            FROM       hcworkorderstat
            WHERE hcworkorderstat.code NOT IN ( 'CL','CANCEL' ))
    GROUP BY  trade.name
) tab1LEFT JOIN
(
    SELECT
        trade.name AS name2,
        COUNT(workorders.id) AS count2
    FROM
        workorders
    RIGHT JOIN
        trade
    ON
        workorders.trade_id = trade.id
    AND
        workorders.requesteddate BETWEEN '1391.01.15' AND '1396.10.15'
    GROUP BY
        trade.name
) tab2
ON tab1.name1=tab2.name2

need results count1+count2 also i need show count 1 and count 2 in table how can do that?

Upvotes: 0

Views: 206

Answers (2)

India.Rocket
India.Rocket

Reputation: 1245

Try this:-

SELECT name1,count1, count2, (count_1+count_2) AS sum_of_counts
FROM
(
SELECT name1,count1, count2, case when count1>0 then count1 else 0 end as count_1, 
case when count2>0 then count2 else 0 end as count_2
FROM
 (
    SELECT
        trade.name AS name1, COUNT(workorders.id) AS count1
    FROM      workorders
    RIGHT JOIN    trade
    ON trade.id = workorders.trade_id 
    AND     workorders.hcworkorderstat_id IN (
            SELECT     hcworkorderstat.id
            FROM       hcworkorderstat
            WHERE hcworkorderstat.code NOT IN ( 'CL','CANCEL' ))
    GROUP BY  trade.name
 ) tab1
LEFT JOIN
 (
    SELECT
        trade.name AS name2,
        COUNT(workorders.id) AS count2
    FROM
        workorders
    RIGHT JOIN
        trade
    ON
        workorders.trade_id = trade.id
    AND
        workorders.requesteddate BETWEEN '1391.01.15' AND '1396.10.15'
    GROUP BY
        trade.name
 ) tab2
ON tab1.name1=tab2.name2
) a;

Upvotes: 0

APC
APC

Reputation: 146239

" i need sum of count 1 and two also i need show count 1 and count 2 in table"

Hmmm, well the only hard about that is the LEFT JOIN means that count2 will be null when there's no match on tab1.name1=tab2.name2. We can't do arithmetic with nulls so you need to default it to zero. Something like this:

SELECT
   name1, count1, count2, count1 + nvl(count2,0) as tot FROM
(
   ...
)

Upvotes: 1

Related Questions