From: | Andy Lewis <alewis(at)themecca(dot)net> |
---|---|
To: | Oliver Elphick <olly(at)lfix(dot)co(dot)uk> |
Cc: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] 2 Table Select |
Date: | 1999-05-27 20:37:52 |
Message-ID: | Pine.LNX.4.05.9905271537410.25975-100000@tmg.themecca.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Thanks, I have it now....
Andy
On Thu, 27 May 1999, Oliver Elphick wrote:
> 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(dot)Elphick(at)lfix(dot)co(dot)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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrei de Oliveira Mosman | 1999-05-27 21:00:09 | |
Previous Message | Guillaume Marçais | 1999-05-27 20:28:56 | Buffered input for big numeric input files |