CODEWITHSUNDEEP
sqldatabasepostgresql
user2280513
user2280513

Reputation: 81

Formulating an SQL statement

I need help formulating an SQL statement.The tables are

video(video_id, description, price, category_id)

category(category_id, description)

The query needs to produce a summary list showing the number of videos that belong to each category. Categories that do not have videoss assigned to them should also be included with a corresponding value of 0.

Any help would be much appreciated

Upvotes: 1

Views: 57

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125424

select
    c.id, c.description,
    coalesce(count(video_id), 0) total
from
    category c
    left join
    video v using(category_id)
group by 1, 2
order by 2

Upvotes: 1

Ujjwal Manandhar
Ujjwal Manandhar

Reputation: 2244

Operation to Use : Join and Aggregate Function

SELECT category.description, COUNT(*) AS TotalVideo FROM category LEFT JOIN video ON category.category_id = video.category_id GROUP BY category.description

Upvotes: 0

Related Questions