Menu
+----------+----------+ | user_id | video_id | +----------+----------+ | 1 | 1 | | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | +----------+----------+ I have a table setup similar to the one above. I would like to return a total count from my query.
For each user_id they need to have a DISTINCT video_id. So above, user_id = 1 would have 2 unique video_id's and user_id = 2 would have 2 unique video_id's. This would make the total 4. I'm not sure the best way to organize my query to achieve the desired result.
Basically for each user_id, I need something like, COUNT(DISTINCT video_id)
I would like the final result just to return total count of everything.
997
To count distinct values, you can use distinct in aggregate function count(). The result i3 tells that we have 3 distinct values in the table.
Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows. SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName FROM yourTableName; To understand the above syntax, let us create a table. Display all records from the table using select statement.
The correct syntax for using COUNT(DISTINCT) is: SELECT COUNT(DISTINCT Column1) FROM Table; The distinct count will be based off the column in parenthesis. The result set should only be one row, an integer/number of the column you're counting distinct values of.
If you want to get the total count for each user, then you will use:
select user_id, count(distinct video_id) from data group by user_id; Then if you want to get the total video_ids then you will wrap this inside of a subquery:
select sum(cnt) TotalVideos from ( select user_id, count(distinct video_id) cnt from data group by user_id ) d See SQL Fiddle with Demo of both.
The first query will give you the result of 2 for each user_id and then to get the total of all distinct video_ids you sum the count.
110
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
