Andy Lewis wrote: >I am trying to get the _total_ number of jobs >that each company has in the db. > >In other words I
wantto get each distinct company >that has jobs in the jobs table and the _total_ number >of jobs that it has in the
jobstable. > >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
ID32B8FAA1 ======================================== "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