SQL Query SUM of a UNION

[PCSAARON]

Just wondering if anyone could help. We are using the MSSQL tool within Crystal Reports to try to create one SQL query to give us results of the SUM of each PartNo from three tables. This SQL query works as individual summations, but we want to sum the parts again at the end.
(The report basically tells how many parts were used from three areas) I know this could be done within Crystal Reports like it is, but is there a way to do it within the SQL instead?? Thanks in advance.


SELECT kitsedittable_0.PartNo, kitsedittable_0.PartDescription, SUM(kitsedittable_0.Qty*kitsforinvoice_0.KitQty) AS Qty, kitsedittable_0.PartPrice, kitsforinvoice_0.Modified FROM kitsedittable kitsedittable_0, kitsforinvoice kitsforinvoice_0 WHERE kitsedittable_0.KitID = kitsforinvoice_0.KitNo GROUP BY PartNo

UNION
SELECT nonstockedparts.PartNo, nonstockedparts.PartDescription, SUM(nonstockedparts.Qty), nonstockedparts.Price, nonstockedparts.Modified From nonstockedparts GROUP BY PartNo

UNION
SELECT partsforinvoice.PartNo, partsforinvoice.PartDescription, SUM(partsforinvoice.Qty), partsforinvoice.PartPrice, partsforinvoice.Modified FROM partsforinvoice GROUP BY PartNo

 

[synapsevampire]

First, avoid the Unions, they're generally horrible for performance and maintenance.

You could approach this within an SP or a View, I'd use something like the following:

SELECT A.PartNo, A.PartDescription, , A.PartPrice, A.Modified,
SUM(kitsedittable_0.Qty*kitsforinvoice_0.KitQty) AS Qty,
(SELECT SUM(nonstockedparts.Qty) From nonstockedparts
where A.partno = nonstockedparts.partno) as nonstockqty,
(SELECT SUM(partsforinvoice.Qty) From partsforinvoice
where A.partno = partsforinvoice.partno) as partsinvoiceqty,
FROM kitsedittable A, kitsforinvoice kitsforinvoice_0 WHERE A.KitID = kitsforinvoice_0.KitNo GROUP BY PartNo

Now this could be created as a View, and the View referenced within another View as:

Select *,(QtyFROM+nonstockqty+partsinvoiceqty) as SumOfAll3 from MyNewView

If what you really want is to return the values in the first View (equivalent to your Union SQL), then provide a row which sums all parts, then you're missing how Crystal works, data sources should be thought of as tables, NOT multiple returnsets.

Hope this clarified, at any rate, you now have greatly improved SQL and understand that Unions are almost always avoidable, and should be.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com