Re: Help for MSSQL "Compute" equivalent in Postgres

#1

"Manfred Koroschetz" <mkoroschetz (AT) tekvoice (DOT) com> writes:

Quote:

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

#2

On Tue, Apr 06, 2004 at 14:37:21 -0400,
Manfred Koroschetz <mkoroschetz (AT) tekvoice (DOT) com> wrote:

Quote:

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.

Quote:

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
;

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#3

Thanks Bruno for the clarification. Will test it on my database, but it looks very promising.
Appreciate your help,
Regards,
Manfred Koroschetz

Quote:

Bruno Wolff III<bruno (AT) wolff (DOT) to> 04/10/2004 10:58:49 AM

On Tue, Apr 06, 2004 at 14:37:21 -0400,
Manfred Koroschetz <mkoroschetz (AT) tekvoice (DOT) com> wrote:

Quote:

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.

Quote:

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
;

#4

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Help for MSSQL "Compute" equivalent in Postgres

comp.databases.postgresql.novice comp.databases.postgresql.novice

Re: Help for MSSQL "Compute" equivalent in Postgres - 04-05-2004 , 11:25 PM

Re: Help for MSSQL "Compute" equivalent in Postgres - 04-10-2004 , 09:58 AM

Re: Help for MSSQL "Compute" equivalent in Postgres - 04-10-2004 , 07:29 PM

Eduphant 2.0 - 04-16-2004 , 05:53 AM