DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth;
	b=FjrewOYdkfEw6XN5UiB+Ywg+5bJtzMEE3g91MBY+HHc/fddxnlCdubq6XA2PzwZGLKVeOmNnCSZP99n1mqiDu3vSvRevUWA+s75JGhcDj6HePMFifUq/EJtp3dh+92aA2LJzT3eGxfgeRBp2OlJZPGdUEiP5ip1U694WKsU3ZaM=
Message-ID: <ddcb1c340708201752q15c61688t7e3f04387c6cdd38@mail.gmail.com>
Date: Tue, 21 Aug 2007 10:52:46 +1000
From: novice <user.postgresql@gmail.com>
To: pgsql-general@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: Join query help
Cc: "Osvaldo Rosario Kussama" <osvaldo_kussama@yahoo.com.br>
In-Reply-To: <46C9E9A7.1080409@yahoo.com.br>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <ddcb1c340708172235w3b07b912od246e76fa9109646@mail.gmail.com>
	<ddcb1c340708172311s47a565a5t49ffb8c14acac7d@mail.gmail.com>
	<46C9E9A7.1080409@yahoo.com.br>

Try 2: Here are my DDLs & DMLs

--  Start

CREATE TABLE record
(
  record_id integer PRIMARY KEY,
  record_date timestamp with time zone NOT NULL
);

INSERT INTO record(record_id, record_date) VALUES ('1', '2007-07-23 11:30:37');
INSERT INTO record(record_id, record_date) VALUES ('2', '2007-07-27 11:30:14');
INSERT INTO record(record_id, record_date) VALUES ('3', '2007-07-17 13:15:03');


CREATE TABLE score
(
  score_id integer PRIMARY KEY,
  score_description character(7) NOT NULL

);

INSERT INTO score(score_id, score_description) VALUES ('0', 'NA');
INSERT INTO score(score_id, score_description) VALUES ('1', 'SAFE');
INSERT INTO score(score_id, score_description) VALUES ('2', 'AT RISK');


CREATE TABLE observation
(
  observation_id integer PRIMARY KEY,
  record_id integer REFERENCES record (record_id),
  score_id integer REFERENCES score (score_id)
);

INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3240', '1', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3239', '1', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3238', '1', '2');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3237', '1', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('2872', '2', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('2869', '2', '2');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('2870', '2', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('2871', '2', '1');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3218', '3', '2');
INSERT INTO observation(observation_id, record_id, score_id) VALUES
('3217', '3', '1');

-- End

By executing

SELECT week,
COUNT(record) as records,
SUM(inspection) as inspections

  FROM
	(
	SELECT TO_CHAR(record.record_date, 'YYYY, WW') as week,	
	RECORD.RECORD_ID AS RECORD,
	COUNT(OBSERVATION_ID) AS INSPECTION
	  FROM OBSERVATION
	  LEFT JOIN record ON record.record_id = observation.record_id
	  GROUP BY RECORD, WEEK
         ) A

  GROUP BY WEEK;

I get

   week   | records | inspections
----------+---------+--------------
 2007, 30 |       2 |            8
 2007, 29 |       1 |            2
(2 rows)


I like to query for a result set that will also have the sum(score_id)
where score_id = '1' like the following

   week   | records | inspections | score
----------+---------+-------------+------
 2007, 30 |       2 |           8 |    6
 2007, 29 |       1 |           2 |    1
(2 rows)

This will help identify that there were 6 SAFE observations found from
the 8 inspections on week 30.

I hope this is not too confusing that I have to re-design the whole schema