Count in SQL

[00:00:00]hi I'm trol a software engineer with 10 years of experience I extensively used the distinct and count functions in various software projects and I have seen firsthand how valuable they are for data analysis and problem solving these functions are essential tools for any software developer and I'm excited to share my knowledge and help you master them let's explore the world of data together imagine you are working with a large data set of customer orders you need to know exactly how many unique products have been ordered and the total number of orders placed how would you go about retrieving this information in this tutorial you'll learn how to use count and distinct functions to answer these questions efficiently these powerful functions will help you extract meaningful insights from your data and make informed decisions the distinct clause in postgress is used to remove duplicate rows from a result set ensuring that only unique rows are returned imagine a buffet table with various dishes the distinct CLA is like selecting only one plate of each unique dish ensuring you have a variety without duplicates you can use the distinct claws in various scenarios including identifying unique values analyzing data distribution eliminating redundancy let's look at the examples using orders table these are all rows in the orders table as you can see we have three unique products with product ID 1 2 and three to find Unique Products ordered we have to use distinct keyword with product ID column we can find unique combinations of products and customers we have two duplicated combinations which are colored with red we have to add all the columns after distinct keyword which we want to find unique combination of on the 1st of January two distinct products with ID 1 and two have been ordered by using we and distinct Clauses together we can find unique orders of a specific date count function allows you to efficiently count the number of rows in a table or the occurrences of specific values within a column you can use the count function in various scenarios such as calculating the total number of Records counting occurrences of specific values performing data aggregation let's revise Buffet analogy for count we need to count all dishes regardless of their types counting all the dishes on the buffet regardless of data type is like using count to count the total number of rows in a table let's see the examples using orders table to count the total number of orders placed we use count function with star argument in the buffet example by combining count with distinct we can count the unique dishes effectively eliminating duplicates by applying the same on orders table we can count the number of unique products ordered we can also count the number of orders for each product depending on the argument count function behaves differently while both count rows star includes row with null values column excludes null values in the specified column and using count star on large tables can be slow due to the need to scan all rows consider using indexes or other optimization techniques for performance improvements that brings us to the end of the lesson see you on the next one peace