Received: from mail.enterprise.net (mail.enterprise.net [194.72.192.18]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA46952 for ; Fri, 4 Jun 1999 03:42:04 -0400 (EDT) (envelope-from olly@lfix.co.uk) Received: from linda.lfix.co.uk (root@max05-058.enterprise.net [194.72.197.58]) by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id IAA07609; Fri, 4 Jun 1999 08:42:02 +0100 (GMT/BST) Received: from linda.lfix.co.uk (olly@localhost [127.0.0.1]) by linda.lfix.co.uk (8.9.3/8.9.3/Debian/GNU) with ESMTP id IAA04924; Fri, 4 Jun 1999 08:41:53 +0100 Message-Id: <199906040741.IAA04924@linda.lfix.co.uk> X-Mailer: exmh version 2.0.2 2/24/98 (debian) To: "Tim Perdue" cc: pgsql-sql@hub.org Subject: Re: [SQL] Group By Dilemma In-Reply-To: Message from "Tim Perdue" of "Thu, 03 Jun 1999 20:47:30 CDT." <001801beae2c$354dd580$0b8c5aa5@timnt.weather.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Fri, 04 Jun 1999 08:41:52 +0100 From: "Oliver Elphick" "Tim Perdue" wrote: >Hello, I am trying to do a GROUP BY so that I can get a count of messages >with the same subject. The problem is, the optimizer won't let me group by >just one column, when I am selecting several columns. You can only have the grouped column and aggregates in your query. >I suppose Postgres doesn't know which rows to leave out and which ones to >display, but I'm hoping someone can offer a solution.... > >SELECT >fld_mailid, >fld_mail_date, >fld_mail_is_followup, >fld_mail_from, >fld_mail_subject, <-- Group only on this, so I can get a count >count(*) >FROM tbl_mail_archive >WHERE fld_mail_list=1 AND >fld_mail_year=1999 AND >fld_mail_month=06 >group by fld_mail_subject >ORDER BY fld_mail_date DESC >LIMIT 26 OFFSET 0; What do you expect to see in the other columns? Why are you including them at all? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "But without faith it is impossible to please him; for he that cometh to God must believe that he is, and that he is a rewarder of them that diligently seek him." Hebrews 11:6