From: | novice <user(dot)postgresql(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org |
Cc: | "Osvaldo Rosario Kussama" <osvaldo_kussama(at)yahoo(dot)com(dot)br> |
Subject: | Re: Join query help |
Date: | 2007-08-21 00:52:46 |
Message-ID: | ddcb1c340708201752q15c61688t7e3f04387c6cdd38@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Glaesemann | 2007-08-21 01:04:10 | Re: Join query help |
Previous Message | D. Dante Lorenso | 2007-08-21 00:00:32 | Re: Using oid as pkey |
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Glaesemann | 2007-08-21 01:04:10 | Re: Join query help |
Previous Message | Jon Horsman | 2007-08-20 15:47:12 | Re: When is a shared library unloaded? |