What is the usage of “partition by” in SQL?

In SQL, PARTITION BY is a clause used to group data. It is often used with window functions to apply functions to each group and partition data based on specified columns.

Here is the syntax:

SELECT column1, column2, ..., function(column)
  OVER (PARTITION BY partition_column)
  FROM table_name;

In the above syntax, the partition_column specified after PARTITION BY determines the column that will be used to group the data. Each group will have its own calculated result.

For example, suppose we have a table ‘sales’ containing sales data with columns: salesperson, product, and quantity. We want to calculate the total quantity of each product sold by every salesperson. We can use PARTITION BY to group the data by salesperson, and then apply the SUM function to calculate the total sales quantity for each group.

Example query:

SELECT salesperson, product, quantity,
       SUM(quantity) OVER (PARTITION BY salesperson) as total_sales
  FROM sales;

The query will return the sales quantities for each product sold by each salesperson and calculate the total sales volume within each group.