Merging rows from union query 1

[hazelsisson]

Hello,
I have a query that produces almost the right output but not quite. If I show you the output first it will probably help rather than trying to decipher my query.

This is the output:
month design_cost external_cost
------ ------------ -------------
05/04 100 200
05/04 (null) 100
06/04 75 150
06/04 (null) 50

But I would like it to be grouped by month, with the two external costs added like this:

month design_cost external_cost
------ ------------ -------------
05/04 100 300
06/04 75 200

Here is my query:

select to_char(date_rec, 'MM/YY') mydate, 
sum(design_cost) design_cost, 
sum(external_cost) external_cost, 
from worksheets
where date_rec between '01-APR-2004' and '31-MAR-2005'
group by to_char(date_rec, 'MM/YY')
union
select to_char(order_date, 'MM/YY') mydate, 
to_number(null) design_cost, 
sum(outwork_company.price) external_cost, 
from orders, outwork_company 
where orders.o_type = 'U'
and orders.OUTWORK_ID = outwork_company.OUTWORK_ID
and orders.order_date between '01-APR-2004' and '31-MAR-2005'
group by to_char(order_date, 'MM/YY')

As you can see from the query the design_cost for the second row in each month is always null.

I've tried combining the two parts of the query and getting rid of the "union" but it was a bit of a disaster!

Any help or suggestions would be great!
Thanks,
Hazel

 

[carp]

Try

SELECT mydate, sum(design_cost), sum(external_cost)
FROM 
(
select to_char(date_rec, 'MM/YY') mydate, 
sum(design_cost) design_cost, 
sum(external_cost) external_cost, 
from worksheets
where date_rec between '01-APR-2004' and '31-MAR-2005'
group by to_char(date_rec, 'MM/YY')
union
select to_char(order_date, 'MM/YY') mydate, 
to_number(null) design_cost, 
sum(outwork_company.price) external_cost, 
from orders, outwork_company 
where orders.o_type = 'U'
and orders.OUTWORK_ID = outwork_company.OUTWORK_ID
and orders.order_date between '01-APR-2004' and '31-MAR-2005'
group by to_char(order_date, 'MM/YY')
)
GROUP BY mydate;

 

[hazelsisson]

That worked perfectly, thanks very much Carp!
Hazel