PostgreSQL how do I COUNT with a condition?-CodePudding

Can someone please assist with a query I am working on for school using a sample database from PostgreSQL tutorial? Here is my query in PostgreSQL that gets me the raw data that I can export to excel and then put in a pivot table to get the needed counts. The goal is to make a query that counts so I don't have to do the manual extraction to excel and subsequent pivot table:

SELECT
i.film_id, 
r.rental_id
FROM
rental as r
INNER JOIN inventory as i ON i.inventory_id = r.inventory_id
ORDER BY film_id, rental_id
;

From the database this gives me a list of films (by film_id) showing each time the film was rented (by rental_id). That query works fine if just exporting to excel. Since we don't want to do that manual process what I need is to add into my query how to count how many times a given film (by film_id) was rented. The results should be something like this (just showing the first five here, the query need not do that):

film_id | COUNT of rental_id
1       | 23
2       | 7
3       | 12
4       | 23
5       | 12

Database setup instructions can be found here: LINK

I have tried using COUNTIF and CASE (following other posts here) and I can't get either to work, please help.

CodePudding user response：

Did you try this?:

SELECT
i.film_id, 
COUNT(1)
FROM
rental as r
INNER JOIN inventory as i ON i.inventory_id = r.inventory_id
GROUP BY i.film_id
ORDER BY film_id;

If there can be >1 rental_id in your data you may want to use COUNT(DISTINCT r.rental_id)