[postgis-users] regarding count query on multiple column in one go

[Paragon Corporation]

Yamini,
 
The first way that comes to mind is just wrap that in a subselect.
 
SELECT orig.*
FROM hydro_net As orig 
    INNER JOIN ( 

select hyd_name, count(*)

from hydro_net

group by hyd_name

having count(*) > 1) As dupes ON (orig.hyd_name = dupes.hyd_name)

 

Leo

http://www.postgis.us


  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Yamini
Singh
Sent: Thursday, February 24, 2011 11:19 AM
To: PostGIS User List
Subject: [postgis-users] regarding count query on multiple column in one go



Hi All,




I have a table hydro_net in PostGIS database. The table has following
schema:

CREATE TABLE public.hydro_net

(hid integer NOT NULL DEFAULT nextval,

  f_code_des varchar(254),

  hyd_desc varchar(254),

  hyd_name varchar(254),

  geom geometry, 

I have more than 3000 records in this table and some records have multiple
hyd_name but with the same geometry. I can count the hyd_name with a simple
query to get the hyd_name and the count as under:

select hyd_name, count(*)

from hydro_net

group by hyd_name

having count(*) > 1

order by hyd_name;

I would like to know if it is possible to get the table out all the columns
in the original table with the records of duplicate hyd_name and its count. 

I am not really getting how to write a query that count the hyd_name and
also returns all column information as well at least hyd_name, geom and
count.

Would really appreciate any help.

Thanks,

Yamini

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110224/ad914a75/attachment.html>