Thanks Bruno for the clarification. Will test it on my database, but it looks very promising.
Appreciate your help,
Regards,
Manfred Koroschetz
>>> Bruno Wolff III<bruno@wolff.to> 04/10/2004 10:58:49 AM >>>
On Tue, Apr 06, 2004 at 14:37:21 -0400,
Manfred Koroschetz <mkoroschetz@tekvoice.com> wrote:
> The Compute by clause of MSSQL basically allows you to get a running
> total at the bottom (end) of the report.
> In a way it is similar then using ".. group by .." with aggregate
> functions (sum) but in this case I am not trying to "... group by .."
> does not make sense in the context of the query, just want to get a
> summary (sum and count) of some columns at the end of the record.
The "standard" way to do this is to make a second query to compute
the aggragates. However it is possible to combine the two if you
really need the aggregates in the same result set.
> > > select A.ProdID, A.Description, A. Qty, A.Price
> > > from SoldItems as A
> > > where A.ListID = 15
> > > order by A.ProdID
> > > compute count(A.ProdID),sum(A.Price),sum(A.Qty)
SELECT ProdID, Description, Qty, Price
FROM
(SELECT A.ProdID, A.Description, A.QTY, A.Price, 1 AS Kind
FROM SoldItems AS A
WHERE A.ListID = 15
UNION ALL
SELECT count(B.ProdID), NULL AS Description, sum(B.Price), sum(B.Qty),
2 AS Kind
FROM SoldItems AS B
WHERE B.ListID = 15
) AS C
ORDER BY Kind, ProdID
;