Wei Weng wrote:
> I made a mistake in the queries:
>
> They should be
>
> update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da =
> 1) as b where tkey = <value>;
> update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da =
> 2) as b where tkey = <value>;
> ...
> update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da =
> 10) as b where tkey = <value>;
You should be able to generate all the counts from one scan:
UPDATE T set t1 = b.a1, t2 = b.a2 ...
FROM (
SELECT
sum(CASE WHEN D.da=1 THEN 1 ELSE 0 END) AS a1,
sum(CASE WHEN D.da=2 THEN 1 ELSE 0 END) AS a2,
...
FROM D
) AS b
WHERE tkey = <value>
You might also want to look at the crosstab functions in the tablefunc
contrib module (see appendix F of the manuals).
--
Richard Huxton
Archonet Ltd
-
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
