How can I get views and unique views from a database table that has only user id and video id in one query?

Question

Say I have a table with columns userId and videoId, representing which user watched which video in a video app. Now I want to get the view count and the unique view count from the table, but using only one query. Is this possible? Here's a visual of the example:

Answer 1

Nevermind I figured it out. I ran the following query:

SELECT 
    video, 
    count(user) AS views, 
    count( DISTINCT user) AS uniqueViews 
FROM <table>
GROUP BY videoId