Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 2174C7634 for ; Tue, 26 Jul 2011 19:34:49 +0000 (UTC) Received: (qmail 94718 invoked by uid 500); 26 Jul 2011 19:34:48 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 94673 invoked by uid 500); 26 Jul 2011 19:34:48 -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 94666 invoked by uid 99); 26 Jul 2011 19:34:47 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 26 Jul 2011 19:34:47 +0000 X-ASF-Spam-Status: No, hits=-2.3 required=5.0 tests=RCVD_IN_DNSWL_MED,SPF_HELO_PASS,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of gcaddu-derby-user@m.gmane.org designates 80.91.229.12 as permitted sender) Received: from [80.91.229.12] (HELO lo.gmane.org) (80.91.229.12) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 26 Jul 2011 19:34:38 +0000 Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1QlnOS-0003YI-NB for derby-user@db.apache.org; Tue, 26 Jul 2011 21:34:16 +0200 Received: from p5481E2C4.dip.t-dialin.net ([84.129.226.196]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 26 Jul 2011 21:34:16 +0200 Received: from Thomas.K.Hill by p5481E2C4.dip.t-dialin.net with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 26 Jul 2011 21:34:16 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: derby-user@db.apache.org From: Thomas Hill Subject: Re: New developer, need a little help with a subquery Date: Tue, 26 Jul 2011 19:34:04 +0000 (UTC) Lines: 12 Message-ID: References: <32142730.post@talk.nabble.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: sea.gmane.org User-Agent: Loom/3.14 (http://gmane.org/) X-Loom-IP: 84.129.226.196 (Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.3) Gecko/20100401 Firefox/3.6.3) X-Virus-Checked: Checked by ClamAV on apache.org if I understand correctly you need a 'group by' in your query; so like SELECT day("dtmDateCompleted") as "Day", "A"."strName" AS "Category", COUNT(*) FROM "tblAssetCategory" "AC" INNER JOIN "tblAsset" "A" ON "AC"."id" = "A"."intCategoryID" INNER JOIN "tblWorkOrder" "W" ON "W"."intAssetID" = "A"."id" WHERE "dtmDateCompleted" IS NOT NULL GROUP BY day("dtmDateCompleted"), "A"."strName"