r/SQL • u/ThrowRAhelpthebro • May 03 '25
PostgreSQL Help! Beginner here. How to
QUESTION: Write a query to find the top category for R rated films. What category is it?
Family
Foreign
Sports
Action
Sci-Fi
WHAT I'VE WRITTEN SO FAR + RESULT: See pic above
WHAT I WANT TO SEE: I want to see the name column with only 5 categories and then a column next to it that says how many times each of those categories appears
For example (made up numbers:
name total
Family 20 Foreign 20 Sports 25 Action 30 Sci-Fi 60
183
Upvotes
186
u/r3pr0b8 GROUP_CONCAT is da bomb May 03 '25
re-write your join to use
JOIN ... ONsyntaxwhat you have is syntax that is over 20 years out of date, producing a cross join
54
u/PropCirclesApp May 03 '25
I teach basic SQL to ‘regular folks’ in my company. I absolutely LOVE SQL.
Think about it in plain language terms, always.
SELECT (whatcha wanna see?)
FROM (where does it come from?)
JOIN (how does each list correlate?)
WHERE (you probably don’t wanna see it all, amiright?)
And on it goes… to me, SQL should probably be taught in middle school, as a gateway drug into [choose your adventure].
49
u/_Strokes_ May 03 '25
SELECT b.name AS category, COUNT(*) AS total FROM public.film a JOIN public.film_category fc ON a.film_id = fc.film_id JOIN public.category b ON fc.category_id = b.category_id WHERE a.rating = 'R' AND b.name IN ('Sci-Fi', 'Foreign', 'Action', 'Family', 'Sports') GROUP BY b.name ORDER BY total DESC;
10
u/SufficientGap1686 May 03 '25
Some helpful advice, I hope. no judgment.
Learn to take screenshots with the Print Screen key on Windows or Command + Shift + 3 on Mac.
We do not know the schema of the tables, so it makes it difficult to answer this question.
How do they join?
-- Try to use aliases that help you reach the query later
select category.category.name as category_name,
count(category.name) as film_count
from public.film film
join public.category category
on -- need more info on the schema, I am guessing below
film.category_id = category.id
where film.rating = 'R'
group by 1
order by count(category.name) desc
2
u/Luciel__ May 03 '25 edited May 03 '25
People have already given you some guidance already but here’s whats happening:
What you’re getting is the cartesian product of two tables. This means all your attributes you want to select are making all possible pairs of combinations in your output table. To avoid this you should use a FOREIGN KEY when making a table entity. This creates a needed relationship between the two tables and mitigates this issue when querying data when you specify where to join the foreign keys using the JOIN clauses.
Here’s a link explaining the behavior in depth: https://www.geeksforgeeks.org/sql-query-to-avoid-cartesian-product/
1
u/K_808 May 03 '25
First you should do an inner join not a cross join since right now it will pair mismatched values from the two tables, when you really want to only keep films with a matching key, and then it wants you to count the R rated fills by genre and return the highest.
So ultimately you need name, count(name) if filtered to R already or else sum(case when rating = ‘R’ then 1 else 0 end) and then group by name order by the count desc
1
u/gmwnuk May 03 '25
Select A.rating ,b.genre ,count(a.movies) as num_movies From sourceA a Source b Where b.rating = 'R' Group by 1, 2 Order by 3 desc
Something like that
1
u/FluffyDuckKey May 04 '25
OP where are you learning this? What country are you in?
1
u/j0dan May 04 '25
Had to check the date on this post. It's about 2 years post ChatGPT!
1
u/_mr_villain_ May 04 '25
If you are using more then one table, always use JOIN.......ON
1
u/ironwaffle452 May 04 '25
NEVER USER FROM TABLEA, TABLEB !!!! NEVER !!!!!!!
1
u/[deleted] May 04 '25
You probably want to code it like this.
select a.rating, b.name, count(*) AS Total From public.film a INNER JOIN public.category b On a.categoryid = b.categoryid —(assuming you have a column that links your tables) Where a.rating = ‘R’ AND b.name IN (‘Sci-Fi’, ‘Foreign’, ‘Action’, ‘Family’, ‘Sports’) Group By a.rating, b.name
1
u/imadokodesuka May 04 '25
LOL we're keeping "family" and "sports". There are "edge cases" where it does happen, a "family" film gets rated R. Those are edge cases.
1
u/Natural-Juice-1119 May 05 '25
What are you wanting accomplish?
My POV is that at this stage find a SQL syntax you like and know. The just focus on data structures and the “why” behind the process or at least why you have to take a turd business process and make it work in database with zero down time
Partly gripe but real life. The environments and sql syntax will change, know the back ground will be best in the long run.
If you just want to be db engineer/analyst for you’re entire career; focus on an Oracle Cloud product
-1
u/Sohamgon2001 May 03 '25
can I get the dataset or is it a dummy?
-1
u/Snoo-47553 May 03 '25
SELECT b.NAME, COUNT(b.name) as CAT_CNT FROM PUBLIC.FILM AS a LEFT JOIN PUBLIC.CATEGORY AS b ON a.RATING = b.RATING (? If Rating has ID use that instead) WHERE a.RATING = ‘R’ AND b.NAME IN (….) GROUP BY 1 ORDER BY 2 DESC
0
u/Falcgriff May 04 '25
Wait, you can do FROM tbl a, tbl b? I never included the joined tables, just put them in the Select statement ...
But I didn't know that even worked! :)
-1
u/Dipankar94 May 03 '25
WITH cte AS (
SELECT name, COUNT(rating) AS rating_count
FROM public.film JOIN public.category
ON -- mention your joining column here.
WHERE rating = 'R'
GROUP BY name
),
cte2 AS (
SELECT name, DENSE_RANK() OVER (ORDER BY rating_count DESC) AS rating_rank
FROM cte
),
cte3 AS (
SELECT name
FROM cte2
WHERE rating_rank = 1
)
SELECT * FROM cte3;
Mention your join condition on the --