From derby-user-return-3033-apmail-db-derby-user-archive=db.apache.org@db.apache.org Sun Dec 18 15:18:23 2005 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 48002 invoked from network); 18 Dec 2005 15:18:23 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 18 Dec 2005 15:18:23 -0000 Received: (qmail 91464 invoked by uid 500); 18 Dec 2005 15:18:22 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 91116 invoked by uid 500); 18 Dec 2005 15:18:21 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 91105 invoked by uid 99); 18 Dec 2005 15:18:21 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 18 Dec 2005 07:18:21 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [192.18.98.34] (HELO brmea-mail-3.sun.com) (192.18.98.34) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 18 Dec 2005 07:18:20 -0800 Received: from phys-epost-1 ([129.159.136.14]) by brmea-mail-3.sun.com (8.12.10/8.12.9) with ESMTP id jBIFHx3F005840 for ; Sun, 18 Dec 2005 08:17:59 -0700 (MST) Received: from conversion-daemon.epost-mail1.sweden.sun.com by epost-mail1.sweden.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) id <0IRP00B018OI33@epost-mail1.sweden.sun.com> (original mail from Bernt.Johnsen@Sun.COM) for derby-user@db.apache.org; Sun, 18 Dec 2005 16:17:59 +0100 (MET) Received: from localhost (vpn-129-159-115-200.Norway.Sun.COM [129.159.115.200]) by epost-mail1.sweden.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) with ESMTP id <0IRP009ES95YX8@epost-mail1.sweden.sun.com> for derby-user@db.apache.org; Sun, 18 Dec 2005 16:17:59 +0100 (MET) Date: Sun, 18 Dec 2005 16:18:37 +0100 From: "Bernt M. Johnsen" Subject: Re: How to COUNT DISTINCT over multiple columns ? In-reply-to: <224f32340512180338p6497529cw6b0fe6f0da381bd3@mail.gmail.com> To: Derby Discussion Message-id: <20051218151837.GA5473@barbar.sun.com> Organization: Sun Microsystems MIME-version: 1.0 Content-type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary=MGYHOYXEY6WxJCY8 Content-disposition: inline User-Agent: Mutt/1.5.10i References: <224f32340512180338p6497529cw6b0fe6f0da381bd3@mail.gmail.com> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --MGYHOYXEY6WxJCY8 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable >>>>>>>>>>>> Thomas Dudziak wrote (2005-12-18 12:38:52): > SELECT COUNT(*) FROM (SELECT DISTINCT person_id, project_id FROM > person_project); >=20 > doesn't work either, I get a >=20 > ERROR 42X01: Syntax error: Encountered "" at line 1, column 80. >=20 > Is there a way to achieve this with Derby ? You're just missing a small detail: select count(*) from (select distinct person_id, project_id from person_pro= ject) as t(a,b); --=20 Bernt Marius Johnsen, Database Technology Group,=20 Sun Microsystems, Trondheim, Norway --MGYHOYXEY6WxJCY8 Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.7 (GNU/Linux) iD8DBQFDpX3MlFBD9TXBAPARAhBDAKCqY2gLqvjGZCW8GaDrCyfG/NVPrACeLRtl Yqnm58pbaIxhC+f5sMalP1Q= =VfeM -----END PGP SIGNATURE----- --MGYHOYXEY6WxJCY8--