Thread: Help for MSSQL "Compute" equivalent in Postgres

"Manfred Koroschetz" <mkoroschetz@tekvoice.com> writes:
> I am having trouble translating the following simple MSSQL query into
> the equivalent Postgres form.

> 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)

This "compute" construct does not exist in the SQL standard, so you'll
have to forgive us for not immediately knowing what it does ...  if
you'd care to specify exactly what behavior you're trying to achieve,
maybe we could help.

            regards, tom lane

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.

Thanks in advance,

Manfred Koroschetz
mkoroschetz@tekvoice.com

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: mkoroschetz@tekvoice.com
Cc: pgsql-novice@postgresql.org
Date: Tue, 06 Apr 2004 00:25:26 -0400
Subject: Re: [NOVICE] Help for MSSQL "Compute" equivalent in Postgres

> "Manfred Koroschetz" <mkoroschetz@tekvoice.com> writes:
> > I am having trouble translating the following simple MSSQL query into
> > the equivalent Postgres form.
>
> > 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)
>
> This "compute" construct does not exist in the SQL standard, so you'll
> have to forgive us for not immediately knowing what it does ...  if
> you'd care to specify exactly what behavior you're trying to achieve,
> maybe we could help.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

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
;

Hi all,

We have released Eduphant 2.0b2 and for the first time we have a
Linux build too.
(Eduphant is a free cross-platform PostgreSQL GUI that is
text-encodings savvy and supports unicode.  It's targeted at both SQL
novices and PostgreSQL developers.)

Our test configuration for Linux is an excruciatingly slow emulation
and we're not sure of what we're seeing.  (It takes 20 minutes to
launch mozilla for instance.)  We'd therefore be happy if some kind
soul with a tinkering spirit could give our Linux version a whirl and
tell us about it off-list.  Requirements are x86 and GTK 2.0.

Everybody is of course welcome to use it, the Mac and Windows
versions are pretty stable.

More info: <http://aliacta.com/products/eduphant.htm>
Download: <http://aliacta.com/download>

Thanks,

Marc