<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<STYLE>.hmmessage P {
PADDING-BOTTOM: 0px; MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 0px
}
BODY.hmmessage {
FONT-FAMILY: Tahoma; FONT-SIZE: 10pt
}
</STYLE>
<META name=GENERATOR content="MSHTML 8.00.7600.16722"></HEAD>
<BODY class=hmmessage>
<DIV dir=ltr align=left><SPAN class=150084122-24022011><FONT color=#0000ff
face=Arial>Yamini,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=150084122-24022011><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=150084122-24022011><FONT color=#0000ff
face=Arial>The first way that comes to mind is just wrap that in a
subselect.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=150084122-24022011><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=150084122-24022011><FONT color=#0000ff
face=Arial>SELECT orig.*</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=150084122-24022011><FONT color=#0000ff
face=Arial>FROM hydro_net As orig </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=150084122-24022011> <FONT
color=#0000ff face=Arial>INNER JOIN (</FONT>
<P style="MARGIN-LEFT: 1in" class=MsoNormal><FONT face=Arial><FONT
color=#0000ff>select hyd_name, count(*)<?XML:NAMESPACE PREFIX = U1
/><U1:P></U1:P><?XML:NAMESPACE PREFIX = O /><O:P></O:P></FONT></FONT></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal><FONT face=Arial><FONT
color=#0000ff>from hydro_net<U1:P></U1:P><O:P></O:P></FONT></FONT></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal><FONT face=Arial><FONT
color=#0000ff>group by hyd_name<U1:P></U1:P><O:P></O:P></FONT></FONT></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal><FONT face=Arial><FONT
color=#0000ff>having count(*) > 1<SPAN class=150084122-24022011>) As dupes ON
(orig.hyd_name = dupes.hyd_name)</SPAN></FONT></FONT></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal><FONT face=Arial><FONT
color=#0000ff><SPAN class=150084122-24022011></SPAN></FONT></FONT> </P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal><FONT face=Arial><FONT
color=#0000ff><SPAN class=150084122-24022011>Leo</SPAN></FONT></FONT></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal><FONT face=Arial><FONT
color=#0000ff><SPAN class=150084122-24022011><A
href="http://www.postgis.us">http://www.postgis.us</A></SPAN></FONT></FONT></P></SPAN></DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT face=Tahoma><B>From:</B> postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Yamini Singh<BR><B>Sent:</B> Thursday, February 24, 2011 11:19
AM<BR><B>To:</B> PostGIS User List<BR><B>Subject:</B> [postgis-users] regarding
count query on multiple column in one go<BR></FONT><BR></DIV>
<DIV></DIV>
<P class=MsoNormal>Hi All,<U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal><BR></P>
<P class=MsoNormal>I have a table hydro_net in PostGIS database. The table has
following schema:<U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal><O:P></O:P></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal>CREATE TABLE
public.hydro_net<U1:P></U1:P><O:P></O:P></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal>(hid integer NOT NULL DEFAULT
nextval,<U1:P></U1:P><O:P></O:P></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal><SPAN
style="mso-spacerun: yes">
</SPAN>f_code_des varchar(254),<U1:P></U1:P><O:P></O:P></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal><SPAN
style="mso-spacerun: yes"> </SPAN>hyd_desc
varchar(254),<U1:P></U1:P><O:P></O:P></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal><SPAN
style="mso-spacerun: yes">
</SPAN>hyd_name varchar(254),<O:P></O:P></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal><SPAN
style="mso-spacerun: yes"> </SPAN>geom geometry,
<U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal><U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal>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:<U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal><O:P></O:P></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal>select hyd_name,
count(*)<U1:P></U1:P><O:P></O:P></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal>from
hydro_net<U1:P></U1:P><O:P></O:P></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal>group by
hyd_name<U1:P></U1:P><O:P></O:P></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal>having count(*) >
1<U1:P></U1:P><O:P></O:P></P>
<P style="MARGIN-LEFT: 1in" class=MsoNormal>order by
hyd_name;<U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal><U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal>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. <U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal>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.<O:P></O:P></P>
<P class=MsoNormal><U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal>Would really appreciate any help.<U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal><U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal>Thanks,<U1:P></U1:P><O:P></O:P></P>
<P class=MsoNormal>Yamini<U1:P></U1:P><O:P></O:P></P></BODY></HTML>