From: | "Manfred Koroschetz" <mkoroschetz(at)rkmus(dot)com> |
---|---|
To: | bruno(at)wolff(dot)to, "Manfred Koroschetz" <mkoroschetz(at)tekvoice(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org, pgsql-novice-owner+M9926=mkoroschetz=rkmus(dot)com(at)postgresql(dot)org, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Help for MSSQL "Compute" equivalent in Postgres |
Date: | 2004-04-11 00:29:15 |
Message-ID: | 20040410T202915Z_B1B3000E0000@rkmus.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(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:
> 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
;
From | Date | Subject | |
---|---|---|---|
Next Message | David Rickard | 2004-04-12 16:24:01 | pg_dump warning message |
Previous Message | Nabil Sayegh | 2004-04-10 15:56:29 | Re: adopendynamic |