GROUP BY difficulties

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

Browse pgsql-novice by date

  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