Instruction
- Write a query to return the number of films that we have inventory vs no inventory.
- A film can have multiple inventory ids
- Each film dvd copy has a unique inventory ids
Table 1: film
col_name | col_type
----------------------+--------------------------
film_id | integer
title | text
description | text
release_year | integer
language_id | smallint
original_language_id | smallint
rental_duration | smallint
rental_rate | numeric
length | smallint
replacement_cost | numeric
rating | text
Table 2: inventory
Each row is unique, inventoy_id is the primary key of this table.
col_name | col_type --------------+-------------------------- inventory_id | integer film_id | smallint store_id | smallint
Sample results
in_stock | count --------------+------- in stock | 123 not in stock | 456
Solution postgres
SELECT in_stock, COUNT(*)
FROM (
SELECT
F.film_id,
MAX(CASE WHEN I.inventory_id IS NULL THEN 'not in stock' ELSE 'in stock' END) in_stock
FROM film F
LEFT JOIN INVENTORY I
ON F.film_id =I.film_id
GROUP BY F.film_id
) X
GROUP BY in_stock;
Explanation
This query is retrieving the count of movies that are "in stock" and "not in stock" from a database table. The query begins by selecting the "in_stock" column and the count of all records.
Then, it uses a subquery that joins the "film" and "inventory" tables based on the "film_id" column. The subquery groups all the records by "film_id" and selects the maximum value of the "in_stock" column based on whether or not the "inventory_id" is null. If the inventory_id is null, then the movie is "not in stock", otherwise it is "in stock".
The outer query groups the results of the subquery by the "in_stock" column, which groups all the movies that are "in stock" and all the movies that are "not in stock" together. Finally, it counts the number of movies in each group and returns the result.
Expected results