Re: Help for MSSQL "Compute" equivalent in Postgres

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
;