Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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




pgsql-novice by date

Next:From: Wolfgang KellerDate: 2009-08-23 07:52:54
Subject: Generate LaTeX doc from PostgreSQL (autodoc)?
Previous:From: Andreas KretschmerDate: 2009-08-22 15:13:15
Subject: Re: How to get the all tables, triggers, fuctionsavailable in my database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group