From: | Michael Rowan <mike(dot)rowan(at)internode(dot)on(dot)net> |
---|---|
To: | postgresql novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | GROUP BY difficulties |
Date: | 2009-08-22 15:43:57 |
Message-ID: | 3D4C1DD3-2B33-423A-B290-EF1AF1553DA0@internode.on.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
The following query successfully selects a row for every property
advertised by selling agents after a date (thanks due to this list
already).
SELECT * FROM (SELECT DISTINCT ON (property_id)
Agent.first_name, Agent.last_name, Agent.id
FROM Advertisement, Agent, Property
WHERE Property.id = Advertisement.property_id
AND Agent.id = Advertisement.agent_id
AND Advertisement.date >= '2009-01-01'
ORDER BY property_id, date DESC) ss ORDER BY Agent.last_name,
Agent.first_name;
I need to add to the query such that rather than returning a number of
identical rows for each Agent, it returns only one row per Agent and
includes each agents row-count.
Any help gratefully received. GROUP BY seems pretty tricky to use.
Michael Rowan
mike(dot)rowan(at)internode(dot)on(dot)net
11 Kingscote Street
ALBERTON
South Australia 5014
tel 618 8240 3993
From | Date | Subject | |
---|---|---|---|
Next Message | Wolfgang Keller | 2009-08-23 07:52:54 | Generate LaTeX doc from PostgreSQL (autodoc)? |
Previous Message | Andreas Kretschmer | 2009-08-22 15:13:15 | Re: How to get the all tables, triggers, fuctions available in my database |