| From: | "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk> |
|---|---|
| To: | Andy Lewis <alewis(at)themecca(dot)net> |
| Cc: | pgsql-sql(at)postgreSQL(dot)org |
| Subject: | Re: [SQL] 2 Table Select |
| Date: | 1999-05-27 19:34:54 |
| Message-ID: | 199905271934.UAA07954@linda.lfix.co.uk |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
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 | Guillaume Marçais | 1999-05-27 20:28:56 | Buffered input for big numeric input files |
| Previous Message | Tom Lane | 1999-05-27 17:18:07 | Re: [SQL] notifying |