Re: SQL count question
Matt,
Instead of using 'where count(*)>1' you should try using 'having'
select fname,count(*) from customer group by fname having count(*)>1
This should accomplish what you want.
-------------------------------------------------------------------------
Joseph Cullipher |E-mail: joseph@cannonexpress.com
PO Box 364 |opinions express are those of my own and
Springdale, AR 72764 USA |don't necessarily reflect those of my company
-------------------------------------------------------------------------
On Fri, 25 Jul 1997, Matt Brickley wrote:
} On SE 5.01, SCO Unix 3.2v4.2
} I am trying to use the aggregate count(*) to select rows where
} there are more than on occurrance.
}
} The Informix guide to SQL on pg 7-377 shows this statement:
}
} select fname,count(*) from customer
} group by fname
} where count(*) > 1
}
} This format gave me a syntax error
}
}
} When I put the where before the group by like this:
}
} select fname,count(*) from customer
} where count(*) > 1
} group by fname
}
} I get a 595 - Bad use of aggregate in this context
}
} How do I do it?
}
} TIA,
} Matt Brickley
}