Mysql queries (complex)

Scenario-1: Using the count or sum with inner join query

Table structures:

videos: id, title, description, date_uploaded

categories: id, category_name

votes: id, videoid, vote, date (vote will range between 1 and 5)

Query:
SELECT v.id as vid, v.title, v.description, v.date_uploaded, c.category_name, v2.total, v2.count FROM videos v inner join
categories c inner join (select videoid, sum(vote) as total, count(*) as count from votes group by videoid) v2 on v.category_id=c.id and v2.videoid=v.id and v.featured=1 and v.published=1 order by v.date_uploaded desc;

Resultset:

vid  title  description    date_uploaded            id    category_name   total  count
12   Ford   inauguration 2011-10-07 01:08:22    3    Inauguration        97     31
9     Nano   new car        2011-11-02 03:15:25    2    Automobile        120    38  

From the above result set, we can get the percentage of votes for videos as: (total / count) * 20
Note: The above query assumes that you have a record in votes table for each video id in videos table.

Scenario-2: Get the count of videos for each category

Table structures:

videos: id, title, category_id

categories: id, category_name

Query:
SELECT c.id, c.category_name, v.total FROM categories c inner join (select category_id, count(*) as total from videos where published=1 group by category_id) v on c.id=v.category_id group by c.id order by c.category_name;

Result: We can get the total number of videos for each category