CODEWITHSUNDEEP
sql
Sorin Cioban
Sorin Cioban

Reputation: 2225

SQL query showing element more than once

I have the following SQL query but it doesn't entirely work because it shows each element more than once.

select c.name, c.director, c.title, count(c.title) as numfilms
from casting c
join casting c1 on c1.name = c.name
Group by c.name, c.director, c.title
Having count (c.title) > 1
order by numfilms

So what it does it shows which actors have made more than 1 film. But unfortunately, this query shows each actor as many times as the number of movies he made. Using distinct doesn't work as actor may have been in different movie with same title and different director.

Upvotes: 1

Views: 3193

Answers (2)

Mike B
Mike B

Reputation: 1166

select c.name, count(c.title) as numfilms
from casting c
Group by c.name
Having count (c.title) > 1
order by numfilms

If you are just looking for the actors names and the number of films they are in you can remove the title and director from the grouping and select.

EDIT: From reading the comment below clarifying the question Richard's first query should be the correct solution.

Upvotes: 4

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

The question is not making sense - the join of casting back to itself is the confusing part- it is completely unnecessary unless you need the full record as shown below.

select c1.name, c1.director, c1.title, numfilms
from
(
    select c.name, count(c.title) as numfilms
    from casting c 
    Group by c.name
    Having count (c.title) > 1
) c
join casting c1 on c1.name = c.name
order by c1.name

This query shows: for each actor who appears in more than one film, show all the actors' films.

On the other hand, if you are only after actor names who have appeared in more than one film, a single pass through casting is all you need.

select c.name, count(c.title) as numfilms
from casting c 
Group by c.name
Having count (c.title) > 1

The confusing part is, if you wanted names only, why add the director and title to the SELECT? Which film of the actor's films to show?

Upvotes: 1

Related Questions