MySQL SQL Queries Counting This is using the Sakila Sample d

MySQL SQL Queries: Counting

This is using the Sakila Sample database which can download here:   

https://dev.mysql.com/doc/sakila/en

**Note that you will be performing a query on multiple tables. See below the instructions for table names.**

Instructions:

Write a query that produces a count of the number of Action films by looking at the inventory, so that if there are 2 copies of a film it needs to be counted twice.

Schema: sakila

List of Tables

Table Name:         film

Column Names:        film_id,      title

Table Name:         inventory

Column Names:    inventory_id,    film_id,    store_id

Table Name:          film_category

Column Names:    film_id       category_id

Table Name:        category

Column Names:        category_id      name

My Failed SQL Query Attempt:

select count(*), f.film_id, fc.category_id, i.inventory_id

from inventory AS i

inner join film AS f

on f.film_id = i.film_id

inner join film_category AS fc

on f.film_id = fc.film_id

group by f.film_id, fc.category_id, i.inventory_id

having fc.category_id=\'1\';

Solution

See by keeping all three things i.e. film_id, category_id and inventory_id you are not able to get the count. To understand this suppose there is a film_id 19 which is in category_id 1 and comes 10 times in inventory. So the count you are expecting is 10 but you will get 10 records of count 1 as you have grouped the query by film_id,category_id and then inventory_id. Since these are 10 records in inventory table so all will have different id\'s and group by on these will group in 10 different sets. That is why this query is giving count(*) as 1 for all.

Try this (Grouped by only on fim_id):

select count(*), f.film_id
from inventory AS i
inner join film AS f
on f.film_id = i.film_id
inner join film_category AS fc
on f.film_id = fc.film_id and
fc.category_id=\'1\'
group by f.film_id;