Subject: aggregate count + where-clause

From: Peter van Oosterom <[email-masked]>
Date: Tue, 13 Jul 93 16:45:02 MET DST

Dear Postgres-group,

Is it possible to use an aggregate function in combination with a
where-clause? For example: I want to count the instances with properties
specified in the where-clause. The relation "rel" is created and populated:
  create rel (a=int4, b=int4)\g
  append rel (a=4, b=6)\g
  append rel (a=1, b=8)\g

Counting all instances works fine:
  Query sent to backend is "retrieve (nr=count{rel.oid})"
  ---------------
  | nr          |
  ---------------
  | 2           |
  ---------------

Counting in combination with the where-clause does not give (my) right
number of instances (again all instances are count instead of only
the instances specified by the where-clause):
  Query sent to backend is "retrieve (nr=count{rel.oid}) where rel.b > 7"
  ---------------
  | nr          |
  ---------------
  | 2           |
  ---------------

Using a temp relation may help, but can this be avoided?
  retrieve into temp_rel (rel.all) where rel.b > 7\g
  retrieve (nr=count{temp_rel.oid})\g
  destroy temp_rel\g

Any suggestions?

Regards, Peter van Oosterom ([email-masked])

---

From: [email-masked]
Date: Wed, 14 Jul 93 04:50:20 -0700

For the count operator to use the where clause, the clause should be
inside the curly braces with the attrib. I tried this and it worked:

* retrieve (nr = count{rel.oid where rel.b > 7}) \g

Query sent to backend is "retrieve (nr = count{rel.oid where rel.b > 7})"

-----------------
| nr		|
-----------------
| 1		|
-----------------

---

From: [email-masked]
Date: Wed, 14 Jul 93 04:54:52 -0700

-----------------
| nr		|
-----------------
| 1		|
-----------------

Jide Kufoniyi
Dept of Geoinformatics,
ITC, P.O.Box 6,
7500AA Enschede, The Netherlands
e-mail [email-masked]