Sql grouping query

Question

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

The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! — jarlh
– jarlh, Commented Sep 20, 2016 at 7:06
Add sample table data, producing the specified expected result! — jarlh
– jarlh, Commented Sep 20, 2016 at 7:09
Can you explain what VIDEOSNUMBER means in your expected output? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 20, 2016 at 7:15

Tim Biegeleisen · Accepted Answer · 2016-09-20 09:26:16Z

1

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

edited Sep 20, 2016 at 9:26

answered Sep 20, 2016 at 8:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kasnady · Accepted Answer · 2016-09-20 07:44:48Z

0

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

edited Sep 20, 2016 at 7:44

answered Sep 20, 2016 at 7:09

Kasnady

2,2893 gold badges26 silver badges35 bronze badges

4 Comments

jarlh Over a year ago

Invalid GROUP BY. Will raise an error in newer MySQL versions. (And return arbitrary results in older versions.)

Zeno Over a year ago

Indipendentely from the error in newer MySQL versions, it outputs the same result of Nebi and @unnikrishnan-r.

Kasnady Over a year ago

@Zeno have you changed the table name to your? can you show us your error ?

Zeno Over a year ago

I have an old version of MySQL, and to me it gives me no error, but the error is not relevant. The result is, unfortunately, not right.

Unnikrishnan R · Accepted Answer · 2016-09-20 08:19:53Z

0

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

edited Sep 20, 2016 at 8:19

answered Sep 20, 2016 at 7:15

Unnikrishnan R

5,0511 gold badge15 silver badges21 bronze badges

3 Comments

Zeno Over a year ago

It outputs the same result of @Nebi 's query.

Zeno Over a year ago

Not the exact output but I think it's the right way to approach the problem, "nesting" a result table in the from clause. Thanks.

Unnikrishnan R Over a year ago

Add a left join to rent table and try again. See the updated script.

Nebi · Accepted Answer · 2016-09-20 09:17:33Z

0

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

edited Sep 20, 2016 at 9:17

answered Sep 20, 2016 at 7:10

Nebi

3262 silver badges10 bronze badges

3 Comments

Zeno Over a year ago

This was one of the solutions I tried, but VIDEOSNUMBER will contain the total number of rents for each movie, not the number of videos that were rented more than one times. Plus, there won't be movies which videos were not rented.

Nebi Over a year ago

@zeno have updated query. Haven't tested it. Their might be a cleaner solution in MySQL. At SQL-SERVER you use Window-Functions for that. In MySQL you may try Group_concat.

Zeno Over a year ago

It outputs something wrong but i think that this could be a good starting point.

Collectives™ on Stack Overflow

Sql grouping query

4 Answers 4

Comments

4 Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related