CODEWITHSUNDEEP
sqlpostgresql
genericname
genericname

Reputation: 177

Return column sorted by 2 other columns with a condition

I have a DB which is simply a table with 3 columns: viewer_id, movie_id, Ranking (INTEGER) (INTEGER) (TEXT)

Where a row in this table represents that a certain viewer has watched a certain movie.

If the viewer hasn't ranked the movie yet- the Ranking value is NULL.

If the viewer did rank the movie than the Ranking value is LIKE\DISLIKE

I need to write a query that returns the top 10 viewers id with the highest views and rating count ordered by views count and then rating count (both in descending order).

So far, I wrote a query that returns a table with viewer_id, movie_watch_count with the correct information.

   SELECT viewer_id , count(*) AS movies_watched 
   FROM viewers_movies_rankings  
   Group By viewer_id

I tried adding another column to this table - "ranking_count" which will count for each viewer_id the number of rows where the Ranking value != null. (So that I will get also the number of movies the viewer ranked, So the only thing after this to do is to sort by those columns )

but everything I wrote didn't work. For example (not working)

SELECT viewer_id , count(*) AS movies_watched,
COUNT(*) AS movies_watched 
HAVING ranking != null 
FROM viewers_movies_rankings  
Group By viewer_id

Can you help me?

Upvotes: 0

Views: 27

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Is this what you want?

SELECT
    viewer_id,
    COUNT(*) AS movies_watched,
    COUNT(ranking) AS ranking_count
FROM viewers_movies_rankings
GROUP BY viewer_id

The default behavior of the COUNT function is that it only counts non NULL values.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You would seem to want:

SELECT viewer_id , count(*) AS movies_watched,
       COUNT(ranking) as movies_ranked
FROM viewers_movies_rankings  
GROUP BY viewer_id ;

COUNT(<expression>) counts the number of times the expression is not NULL -- and that is exactly what you want to do.

Upvotes: 1

Related Questions