pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: novice <user.postgresql@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Join query help
Date: Sat, 18 Aug 2007 15:35:12 +1000
Message-ID: <ddcb1c340708172235w3b07b912od246e76fa9109646@mail.gmail.com> (raw)

Hi, We have the following three tables.

safety=> SELECT record_id, record_date FROM record;
 record_id |      record_date
-----------+------------------------
         1 | 2007-07-23 11:30:37+10
         2 | 2007-07-27 11:30:14+10
         3 | 2007-07-17 13:15:03+10
(3 rows)

safety=> SELECT observation_id, record_id, score_id FROM observation;
 observation_id | record_id | score_id
----------------+-----------+----------
           3240 |         1 |        1
           3239 |         1 |        1
           3238 |         1 |        2
           3237 |         1 |        1
           2872 |         2 |        1
           2869 |         2 |        2
           2870 |         2 |        1
           2871 |         2 |        1
           3218 |         3 |        2
           3217 |         3 |        1
(10 rows)

safety=> SELECT * FROM SCORE;
 score_id | score_description
----------+-------------------
        0 | NA
        1 | SAFE
        2 | AT RISK
(3 rows)


What query do I write to generate the following?

week_no | count(record_id | count(observation_id) | sum(score_id) where = '1'
2007, 30  |   2   |   8   |   6
2007, 29  |   1   |   2   |   1

view thread (9+ messages)  latest in thread

Message-ID: <ddcb1c340708172235w3b07b912od246e76fa9109646@mail.gmail.com>
Permalink:  ../ddcb1c340708172235w3b07b912od246e76fa9109646@mail.gmail.com/
Also on:    postgresql.org/message-id/ddcb1c340708172235w3b07b912od246e76fa9109646@mail.gmail.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: user.postgresql@gmail.com
  Subject: Re: Join query help
  In-Reply-To: <ddcb1c340708172235w3b07b912od246e76fa9109646@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox