An easy solution is to add another select on top of your current one:
select bldg,year, sum(labour), sum(parts),sum(other)
from (
your original select)
group by bldg, year
--
Terry Dykstra
Canadian Forest Oil Ltd.
"Tim Marshall" <TIMMY!@PurplePandaChasers.Moertherium> wrote in message
news:dgf2uj$ci5$1_at_coranto.ucs.mun.ca...
> I am dealing with an application in which I have a table for work orders
> (which describes jobs performed) and three separate tables for
> transaction against the work order rows, one for labour, one for parts
> and another for other charges.
>
> What I am trying to do is create an output that shows me:
>
> Building Fiscal Year Labour Parts Other Charges
> Building 1 2001 $100 $200 $50
> Building 1 2002 $200 $230 $90
>
> etc.
>
> I'm using a union query, a simplified example of which I have below
> (I've omitted other tables and joins and have only included two select
> statements for labour and other charges instead of the three for labour,
> parts, other charges). However, what it gives me is something like:
>
> Building Fiscal Year Labour Parts Other Charges
> Building 1 2001 $100 0 0
> Building 1 2002 $200 0 0
> Building 1 2001 0 $200 0
> Building 1 2002 0 $230 0
> Building 1 2001 0 0 $50
> Building 1 2002 0 0 $90
>
> Is there anyway I can use Oracle SQL to achieve what I want? Am I
> approaching this the wrong way? I don't wish to be spoonfed, so if
> there is an obvious technique I am missing here just let me know what it
> is and I can research it on my own.
>
> Thanks very much in advance and I hope the length of the statement below
> is not excessive for a news group post.
>
> select
> FB_BLDG_NAME "Building", sum(FWOR_EXT_COST) "Labour", 0 "Other",
> to_char(fwor_trans_date, 'YYYY') "Fiscal Year"
> from
> tma.f_workorder, tma.f_wo_labor
> where
> fwor_trans_date >= to_date('1-APR-2002') and fwor_trans_date <=
> to_date('1-SEP-2005') and wo_pk = fwor_wo_fk
> group by
> FB_BLDG_NAME, to_char(fwor_trans_date, 'YYYY')
> union
> select
> FB_BLDG_NAME "Building", 0 "Labour", sum(ex_inv_amt) "Other",
> to_char(fwor_trans_date, 'YYYY') "Fiscal Year"
> from
> tma.f_workorder, tma.f_wo_charges
> where
> ex_trans_date >= to_date('1-APR-2002') and ex_inv_date <=
> to_date('1-SEP-2005') and wo_pk =ex_wo_fk
> group by
> FB_BLDG_NAME, to_char(fwor_trans_date, 'YYYY')
> --
> Tim Marshall
> Manager Work Control (709) 737-2662
> Facilities Management, Memorial University
> St. John's NL Canada
>
> --
> Tim http://www.ucs.mun.ca/~tmarshal/
> ^o<
> /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
> /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Received on Fri Sep 16 2005 - 14:20:46 CDT
