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-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
