Обсуждение: 2 Table Select
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 | ... +--------------+---------------+-------+ Thanks Andy
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
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.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 > >
At 22:34 +0300 on 27/05/1999, Oliver Elphick wrote: > > 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; If it's only the company_id that interests you, and not the company name or other details in the company table, you can give up the company table altogether. The company_id appears in the jobs table, and there is no point in selecting c.company_id when the condition was that c.company_id is equal to j.company_id. In other words, if all you need is company_id, you can use: select company_id, count(job_id) from jobs group by company_id; Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma