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?

Recommended Answers

All 2 Replies

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
commented: Very Helpful. Instant solve +0
commented: Well done +11

That's fantastic. Thanks a lot : )

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.