Received: from mail.enterprise.net (mail.enterprise.net [194.72.192.18]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA48733 for ; Thu, 27 May 1999 15:44:20 -0400 (EDT) (envelope-from olly@lfix.co.uk) Received: from linda.lfix.co.uk (root@max04-003.enterprise.net [194.72.196.123]) by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id UAA26845; Thu, 27 May 1999 20:44:18 +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 UAA07954; Thu, 27 May 1999 20:34:54 +0100 Message-Id: <199905271934.UAA07954@linda.lfix.co.uk> X-Mailer: exmh version 2.0.2 2/24/98 (debian) To: Andy Lewis cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] 2 Table Select In-Reply-To: Message from Andy Lewis of "Thu, 27 May 1999 11:23:59 CDT." Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Thu, 27 May 1999 20:34:54 +0100 From: "Oliver Elphick" Andy Lewis wrote: >I am trying to get the _total_ number of jobs >that each company has in the db. > >In other words I want to get each distinct company >that has jobs in the jobs table and the _total_ number >of jobs that it has in the jobs table. > >What would be the best query that I could use? > >I'm using 6.4.2. > >Table = jobs >+-------------+---------------+-------+ >| Field | Type | Length| >+-------------+---------------+-------+ >| job_id | int4 not null | 4 | >| company_id | int4 | 4 | >| title | varchar() | 50 | >... >+-------------+---------------+-------+ > >Table = company >+--------------+---------------+-------+ >|Field |Type | Length| >+--------------+---------------+-------+ >| company_id | int4 not null | 4 | >| company_name | varchar() | 100 | >| email | varchar() | 100 | >... >+--------------+---------------+-------+ select c.company_id, count(j.job_id) from company as c, jobs as j where c.company_id = j.company_id group by c.company_id; -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "All that the Father giveth me shall come to me; and him that cometh to me I will in no wise cast out." John 6:37