Using the COUNT/GROUP BY/JOIN Combination in SQL

Counting instances of an entry in SQL

Source: Photo by ColossusCloud from Pixabay

It is often the case that when working with a table in SQL, one wishes to count the number of instances in that table. This could be of a product category, brand, etc.

This can prove simple enough when working with one table. However, the task can be somewhat cumbersome when working across multiple tables.

Here, we will look at an example of how to combine the COUNT and GROUP BY functions with an INNER JOIN to count the number of different product types across a particular retailer.

Clothing Store Example

Let us suppose that a busy clothing store has several tables stored in a database containing all the items stored in their warehouse at any given time. These tables contain information on the name of the item, price, brand, product type, among others. The owner is having difficulty in estimating the specific number of each product in storage, i.e. how many pairs of jeans, how many t-shirts are stored in the warehouse?

Consider the following two tables:

>>> select * from clothes1 limit 1;
item                | colour   | type 
--------------------+----------+----------
Grass Green T-Shirt | Green    | T-Shirt
>>> select * from clothes2 limit 1;
item               | price   | size 
-------------------+---------+--------
Sky Blue Jeans     |  79.99  | 31

Assume the following scenario. clothes1 represents all the clothing items that have ever been present in the warehouse. clothes2 represents all the clothing items that are present in the warehouse at any one time.

Given that only the clothes1 table contains information regarding the type, this means that the first table must be joined with the second table in order to identify each item present in the first table by their type.

Since we only want to count the type of the clothing items that are currently present, and not those which are stored in the second table but not present, we use an INNER JOIN as opposed to a FULL JOIN for this purpose.

Using a FULL JOIN would return all entries from clothes1, i.e. those that have ever been present in the warehouse. Since we are only interested in counting current inventory, we use an INNER JOIN instead.

Query

Here is the query:

>>> select t1.type, count(*) from clothes1 as t1 inner join clothes2 as t2 on t1.item=t2.item group by t1.type;

Resulting from this query, we obtain a table that resembles as follows:

   type    | count 
-----------+-------
 T-Shirt   |  2496
 Jeans     |  3133
 Sneakers  |  2990
 Shirts    |  3844
 Ties      |  1789
 Trousers  |  2500
(6 rows)

Conclusion

In this short tutorial, you have seen how the COUNT/GROUP BY/JOIN combination can be used in SQL to aggregate entries across multiple tables.

While a GROUP BY query can accomplish this simply enough when working with just one table, the situation can become more complex when working across multiple tables.

Many thanks for reading, and any questions or feedback are greatly appreciated! You can also find the original article here, along with further examples of useful SQL practices.

Disclaimer: This article is written on an “as is” basis and without warranty. It was written with the intention of providing an overview of data science concepts, and should not be interpreted as professional advice. The findings and interpretations in this article are those of the author and are not endorsed by or affiliated with any third-party mentioned in this article.