SELECT command to calculate percentage

Question

I'm trying to get the percentage of each video I have in my database based on its view count against all other videos.

I'm then trying to display all the videos from highest view count to lowest, displaying its percentage on its side inside a nice HTML page.

Obviously the percentage would range from 0 - 100% (and not over) and the most popular video would probably have 100% I assume..

I have about 3,400 videos in the database. My attempts are laughable and have been scratching my head for about days now..

My table looks something similar to this.

video_public
id | video_title | video_views

Attempt:

SELECT 
   id, 
   video_views * 100 / (SELECT COUNT(*) FROM video_public)
FROM `video_public` stat

To be honest I don't even know if this SQL query is right.

I haven't even taken into consideration the videos views against all video views and total videos..

Really stuck..

Solution 1

Okay, based on the clarification of your question:

You want to calculated (video_views * 100) / (largest_views_for_any_single_video) for each video in the database.

The numerator is easy, it's just the video_views column. The denominator is

SELECT MAX(video_views) FROM video_public

So, put it together and you get:

SELECT video_title, ((video_views * 100) / (SELECT MAX(video_views) 
FROM video_public)) FROM video_public

That should produce 100 for the most-viewed video(s), and lower percentages for other videos, down to 0 for anything never viewed.

Solution 2

To modify what sheepsimulator suggests, you might try:

SELECT 
    id, 
    video_title, 
    video_views, 
    (select sum(video_views) from video_public)) as TotalViews,
    ((100 * video_views)/(select sum(video_views) from video_public)) as PercentOfViews
FROM 
    video_public 
order by 
    video_views.

Change ordering to suit your tastes, of course.

Solution 3

Well, I'd start by thinking about what your'e looking for:

percentage of each video I have in my database based on its view count against all other videos.

Basically, you want to find it's view rank first. Why not sort the records based on video views:

SELECT id, video_title, video_views
FROM video_public order by video_views DESCENDING

Now, and I think this is what you want to do, is to only show a portion of these, say, the top 10%? You want to then assign each of your records a percentile. This means that for the ordering you've assigned to the videos, you want to make the "top row" (first one returned) be given 100% and the last row returned 0%. It gives a number between 0 and 100 to each item in your resultset.

Your'e percentile is computed:

SELECT id, 
       video_title, 
       video_views, 
       ((video_views * 100) / (select max(video_views) from video_public)) video_percentile
FROM video_public order by video_views DESCENDING

If you only want to show then the top 10%, try the following:

SELECT id, 
       video_title, 
       video_views, 
       ((video_views * 100) / (select max(video_views) from video_public)) video_percentile
FROM video_public
WHERE ((video_views * 100) / (select max(video_views) from video_public)) > 90
ORDER BY video_views DESCENDING

It isn't totally clear what you're looking for, but I think this could be helpful.

---

EDIT: After looking over the comments, specifically Riven's and Larry Lustig's, and re-reading the question, I'd have to say that the sum() of the video_views is incorrect, so I went back and changed the sum()s to max()s. This will give you a percentage based upon the video viewed the most.

Solution 4

If you'd want calculate the percentage in 1 query, use:

SELECT `vp1`.`id`, `vp1`.`video_views` * 100 / (SELECT MAX(`vp2`.`video_views`) FROM video_public AS `vp2`) FROM video_public AS `vp1`

Ofcourse it would be much more efficient to store the intermediate result in PHP (or a SQL variable) and pass it to the next query

$phpmax <= SELECT MAX(`vp2`.`video_views`) FROM video_public AS `vp2`
SELECT `vp1`.`id`, `vp1`.`video_views` * 100 / {$phpmax} ) FROM video_public AS `vp1`

==> Everybody using SUM(views) in the query has the wrong results !! The highest ranking video should result in 100%, not a percentage of the view count of all videos combined, therefore you must use MAX(views)

Solution 5

select id, ((video_views * 100) / (select sum(views) from videos)) view_percent   from video_public

this will give you what percentage of the total views has each video.

View more solutions