Re: Unions With Grouping Group Values

On Fri, 16 Sep 2005 15:59:39 -0230, Tim Marshall <TIMMY!@PurplePandaChasers.Moertherium> wrote:

>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

You've got two subquery's, each producing one record per building/year combination. Use them as inline views and join them, something like this:

SELECT labor.building,labor.year,labor.summ,other.summ FROM
(SELECT building,year,sum(..) summ
 FROM tma.f_workorder, tma.f_wo_labor
 WHERE ...
 GROUP BY building,year) labor,
(SELECT building, year, sum(..) summ
 FROM tma.f_workorder, tma.f_wo_charges
 WHERE ...
 GROUP BY building,year) other
WHERE labor.building = other.building
AND labor.year = other.year

If not all building/year combinations of labor are present in other and/or vice versa, use some form of outer join.

Jaap. Received on Sat Sep 17 2005 - 04:30:05 CDT