I assumed you had discrete values.
If you want to union overlapping values the rules for union are like those
for distinct (afaik) - ie it will combine values where all the fields are
identical - otherwise how does the server establish a mechanism for
combining the other values.
If you need to sum across two tables you are in for a two-step process or
complex SQL that will potentially bring the server to it's knees - like
correlated subqueries. A full outer join would give you the values but in
three columns, a view on a simple union with handed outer joins to the other
tables may give you the result you need, however I haven't ever needed to do
this so it's only theory.
Max
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Peter's Hotmail
Sent: Monday, 9 August 1999 20:20
To: Multiple recipients of list database
Subject: Re: [DUG-DB]: SQL Problem
That's only doing a group by on the 2nd select, not the whole union.
----- Original Message -----
From: Max Renshaw-Fox <[EMAIL PROTECTED]>
To: Multiple recipients of list database <[EMAIL PROTECTED]>
Sent: Monday, August 09, 1999 12:22 PM
Subject: RE: [DUG-DB]: SQL Problem
> Interbase supports:
>
> SELECT CAST(0 AS Integer) MyName,
> CAST(0 As Numeric(12,3)) Totals
> FROM StockUsage
> UNION
> SELECT StockItemID, SUM(UsageAmount)
> FROM StockUsage
> GROUP BY StockItemID
>
> what is the database you are using?
>
> Max
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Darren J Gilbertson
> Sent: Monday, 9 August 1999 11:49
> To: Multiple recipients of list database
> Subject: [DUG-DB]: SQL Problem
>
>
> I would like to create a query using both UNION and GROUP BY.
>
> I plan to combine to tables, then perform a group by on the combination.
How
> do I do this?
>
> I have only managed to do a group by on the individual tables then combine
> them, but I want to combine the tables first then do a group by.
>
> Can somebody please help me.
>
>
> Darren Gilbertson
> [EMAIL PROTECTED]
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
