CODEWITHSUNDEEP
mysqlsql
Zeno
Zeno

Reputation: 43

Sql grouping query

I'm stuck with an SQL query. I know what to do but I can't figure out how. So, here is the scheme needed:

Movie (title, director, year, genre, rate);

TITLE         DIRECTOR     YEAR      GENRE      RATE 
Fight club    Fincher      1999      Action     4.5
Vertigo       Hitchock     1958      Drama      5
Donnie darko  Kelly        2001      Thriller   3.5

Video(title, director, colloc); 

TITLE          DIRECTOR    COLLOC
Fight club     Fincher     3877
Fight club     Fincher     3878
Vertigo        Hitchcock   5431
Vertigo        Hitchcock   5432
Donnie darko   Kelly       9986

Rent(colloc, dateRent, customer, dateReturn);

  COLLOC      DATERENT     CUSTOMER     DATERETURN 
  3877        2016-05-02   324          2016-05-04
  3877        2016-05-20   365          2016-05-20
  3878        2016-04-11   876          2016-04-12
  3878        2016-06-06   112          2016-06-08
  ...         ...          ...          ...
  ...         ...          ...          ...
  9986        2016-02-24   443          2016-02-28

And here is the query:

List, for each movie, how many videos were rented at least two times.

(Note: the store generally has more videos - dvds, vhs, etc. - for each movie).

My approach is the following: I would start with this simple query

SELECT colloc, title, director, COUNT(colloc) AS rentNumber
FROM Rent
NATURAL JOIN Video
GROUP BY colloc

To display something like this:

COLLOC    TITLE         DIRECTOR    RENTNUMBER
3877      Fight club    Fincher     2
3878      Fight club    Fincher     2
5432      Vertigo       Hitchcock   2
5431      Vertigo       Hitchcock   1
9986      Donnie darko  Kelly       1

In order to get to this:

TITLE          DIRECTOR      VIDEOSNUMBER
Fight club     Fincher       2
Vertigo        Hitchcock     1
Donnie darko   Kelly         0

But I'm missing this last step, I can't appropriately use GROUP BY / HAVING. No success even with subqueries. I've been trying to use a different approach but I came out with nothing good, so any tips would be appreciated.

Thanks.

Edit: NOTE, there's no need to use the table Movie in the query. I put it there just to explain that a movie can have more than one video. Added table examples. Primary keys are in bold.

SOLUTION: Thanks to your tips i found a solution (to be honest it's partial, since it lets out all the films for which there are no videos that were rented at least 2 times). Anyway, here it is: 

SELECT title, director, COUNT(title) AS videosNumber
FROM (SELECT colloc, title, director, COUNT(colloc) AS rentNumber
      FROM rent NATURAL JOIN video
      GROUP BY colloc
      ) X
WHERE rentNumber > 1
GROUP BY title

Upvotes: 2

Views: 100

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

Try this query:

SELECT t1.TITLE,
       t1.DIRECTOR,
       COALESCE(t2.VIDEOSNUMBER, 0) AS VIDEOSNUMBER
FROM Movie t1
LEFT JOIN
(
    SELECT v.TITLE, COUNT(DISTINCT r.COLLOC) AS VIDEOSNUMBER
    FROM Rent r
    INNER JOIN Video v
        ON r.COLLOC = v.COLLOC
    GROUP BY v.TITLE
) t2
   ON t1.TITLE = t2.TITLE
ORDER BY COALESCE(t2.VIDEOSNUMBER, 0) DESC

Upvotes: 1

Nebi
Nebi

Reputation: 326

Tested it:

SELECT m.TITLE, m.DIRECTOR, COALESCE(rs.Rentnumber, 0) AS VIDEOSNUMBER
FROM Movie AS m
LEFT JOIN 
(
    SELECT 
        --r.colloc, -- if you want to see r.colloc
        v.TITLE, 
        v.DIRECTOR, 
        COUNT(v.TITLE) AS Rentnumber
    FROM Video AS v
    LEFT JOIN Rent as r on v.colloc = r.colloc 
    GROUP BY r.colloc, v.TITLE, v.DIRECTOR
) AS rs ON m.title = rs.title AND m.director = rs.director
Group by m.title, m.director

Upvotes: 0

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with he below script. You should include the columns in the SELECT list in GROUP BY clause.

SELECT TITLE,DIRECTOR,MIN(VIDEOSNUMBER) VIDEOSNUMBER
FROM (
        SELECT v.colloc,v.TITLE,v.DIRECTOR ,COUNT( r.colloc) VIDEOSNUMBER    
        FROM Video v 
        LEFT JOIN Rent r on  v.colloc=r.colloc
        GROUP BY  v.TITLE,v.DIRECTOR,v.colloc)t
GROUP BY TITLE,DIRECTOR

Upvotes: 0

Kasnady
Kasnady

Reputation: 2279

Try this

SELECT 
-- if you want to add colloc, uncomment below
-- r.colloc as COLLOC
m.title as TITLE,
m.director as DIRECTOR,
COUNT(r.colloc) as VIDEOSNUMBER

FROM movie m
LEFT JOIN video v ON m.title = v.title
LEFT JOIN rent r ON v.colloc = r.colloc
GROUP BY TITLE

Upvotes: 0

Related Questions