From derby-user-return-2783-apmail-db-derby-user-archive=db.apache.org@db.apache.org Mon Nov 28 15:57:24 2005 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 30153 invoked from network); 28 Nov 2005 15:57:24 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 28 Nov 2005 15:57:24 -0000 Received: (qmail 15792 invoked by uid 500); 28 Nov 2005 13:10:43 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 15768 invoked by uid 500); 28 Nov 2005 13:10:43 -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 15750 invoked by uid 99); 28 Nov 2005 13:10:43 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 28 Nov 2005 05:10:43 -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.42.14] (HELO nwkea-mail-2.sun.com) (192.18.42.14) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 28 Nov 2005 05:12:12 -0800 Received: from phys-epost-1 ([129.159.136.14]) by nwkea-mail-2.sun.com (8.12.10/8.12.9) with ESMTP id jASDAK4u020116 for ; Mon, 28 Nov 2005 05:10:21 -0800 (PST) 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 <0IQO00K011GD6N@epost-mail1.sweden.sun.com> (original mail from Bernt.Johnsen@Sun.COM) for derby-user@db.apache.org; Mon, 28 Nov 2005 14:10:20 +0100 (MET) Received: from localhost (atum01.Norway.Sun.COM [129.159.112.201]) by epost-mail1.sweden.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) with ESMTP id <0IQO00D5E1X8KS@epost-mail1.sweden.sun.com> for derby-user@db.apache.org; Mon, 28 Nov 2005 14:10:20 +0100 (MET) Date: Mon, 28 Nov 2005 14:10:19 +0100 From: "Bernt M. Johnsen" Subject: Re: how to count total number of records in each group , when using group by statement. In-reply-to: <20051128093744.GA12100@atum01.norway.sun.com> To: Derby Discussion Message-id: <20051128131019.GB12100@atum01.norway.sun.com> Organization: Sun Microsystems MIME-version: 1.0 Content-type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary=aM3YZ0Iwxop3KEKx Content-disposition: inline User-Agent: Mutt/1.5.10i References: <438AC9E7.6050408@gmail.com> <438A13A0.9070506@gmail.com> <20051128093744.GA12100@atum01.norway.sun.com> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --aM3YZ0Iwxop3KEKx Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Correction: Since year(), month() etc is not standard SQL, but scalar functions defined in JDBC/ODBC escape syntax, the portable syntax for this= would be: instead of > ij> select h,count(*) from (select hour(d) from x) as t(h) group by h; write: select h,count(*) from (select {fn hour(d)} from x) as t(h) group by h; and instead of > ij> select year(d),month(d),day(d),hour(d),minute(d),second(d) from x; write: select {fn year(d)},{fn month(d)},{fn day(d)},{fn hour(d)},{fn minute(d)},{= fn second(d)} from x; -------------------- Furthermore: year(), month etc in Derby returns 0 if the datetime values is NULL, so if the column contains NULL values the first query is wrong and should be select h,count(*) from (select {fn hour(d)} from x where d is not null) as = t(h) group by h; However: if hour(d) is to behave like standard SQL extract(hour from d), then hour(d) should be NULL when d is null. I think there is a bug in Derby here. Anyway, if hour(d) *had* returned NULL, the proper query would have been select h,count(h) from (select {fn hour(d)} from x) as t(h) group by h; since count(h) should ignore NULL values while count(*) should not. --=20 Bernt Marius Johnsen, Database Technology Group,=20 Sun Microsystems, Trondheim, Norway --aM3YZ0Iwxop3KEKx Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFDiwG7lFBD9TXBAPARArQ4AJ0b4+PuxiNp/eDQxfO0PTJj4vRPKQCgwSAr f13iivlpMo/n10UDxbR9yLA= =B7X5 -----END PGP SIGNATURE----- --aM3YZ0Iwxop3KEKx--