0

I want to select from a table only when a field repeats more than once. For this I have:

Select title, Count(*) As Cnt From poss_titles where Cnt > 1 Group By Title Order By Cnt desc

But it says invalid column name cnt. How should I refer to this dynamic field?
Also, I only really want titles which dont exist in another table. I believe a left join is required for this but I'm not too sure how to do it.
Can anyone offer me some pointers?

2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by benkyma
3

You can't use the alias in the boolean test plus you should use a having clause to test the count(*) and not the where clause.

Select title, count(*) As Cnt 
From poss_titles 
Group By title
Having count(*) > 1 
Order By count(*) desc

as for only showing fields that don't exist in another table you can use exists condition.

Select title, count(*) As Cnt 
From poss_titles pt
Where not exists (select title from other_table ot where ot.title = pt.title)
Group By title
Having count(*) > 1 
Order By count(*) desc

Edited by cgyrob: n/a

Votes + Comments
Well done
Very Helpful. Instant solve
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.