Introduction

Aggregate functions are a very powerful tool to analyze the data and gain useful business insights. The most commonly used SQL aggregate functions includeSUM, MAX, MIN, COUNT and AVERAGE. Aggregators are very often used in conjunction with Grouping functions in order to summarize the data. In this story, I will show you how to use a combination of aggregate function and grouping functions.

Preparing some sample Data

Let’s parepare some sample data for this lesson using these scripts below. You can use different platforms of your choice, for instances SQL FIDDLE, KHAN ACADEMY etc.

CREATE TABLE Customer (id INTEGER , name TEXT, product TEXT, OwnershipPercentage numeric(4,3) , Effective_date numeric);
INSERT INTO Customer VALUES (1, “BankA”, “A01”, 0.028, 20180223) ;
INSERT INTO Customer VALUES (1, “BankA”,”A02", 0.018, 20181224) ;
INSERT INTO Customer VALUES (2, “BankB”,”B01", 0.025, 20190101) ;
INSERT INTO Customer VALUES (2, “BankB”,”B02", 0.045, 20200101) ;
select * from Customer;

Table Customer

Problem

Now you see that for each customer, there are multiple (in this case, 2) records for OwnershipPercentage and effective dates with regard to different Products. Let’s say, I need to produce a report for my boss and want to do some data analytics to back my report up. Depending on what I want to see, I will use different aggregate functions. In this lesson, I will give examples using SUM() and MAX() functions. You can come up with different scenarios to play around with other functions.

#sql-server #sql #aggregation