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

[Yamini Singh]

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/06b5b805/attachment.html>