Basic Aggregation Functions

Sum,Min,Max,Only,mode,firstsortedvalue,minstring,maxstring,firstvalue,lastvalue,concat,count,NumericCount,TextCount,MissingCount are the basic aggregate functions.

SUM:

abc:
LOAD Customer,
month(Month) as Month,
PurchasedArticle,
OrderDate,
City,
Name,
Item,
Sales
FROM
abc.xlsx
(ooxml, embedded labels, table is Sheet1);

SUM:
Load Month, sum(Sales) as SalesPerMonth

//from abc.csv group by month;
Resident abc Group by Month;

drop table abc;

sum.PNG

sum1.PNG

Download the file from here >>> abc

 

MIN:

abc:
LOAD Customer,
month(Month) as Month,
PurchasedArticle,
OrderDate,
City,
Name,
Item,
Sales
FROM
abc.xlsx
(ooxml, embedded labels, table is Sheet1);

Min1:
//Load Month, min(Sales) as SmallestSalePerMonth from abc.csv group by Month;
Load Month, min(Sales) as SmallestSalePerMonth Resident abc group by Month;

Min2:
//Load Month, min(Sales, 2) as SecondSmallestSalePerMonth from abc.csv group by Month;
Load Month, min(Sales, 2) as SecondSmallestSalePerMonth Resident abc group by Month;

drop table abc;

Min.PNG

min1

 

MAX:

abc:
LOAD Customer,
month(Month) as Month,
PurchasedArticle,
OrderDate,
City,
Name,
Item,
Sales
FROM
abc.xlsx
(ooxml, embedded labels, table is Sheet1);

Max1:
//Load Month, max(Sales) as LargestSalePerMonth from abc.csv group by Month;
Load Month, max(Sales) as LargestSalePerMonth Resident abc group by Month;
Max2:
//Load Month, max(Sales, 2) as SecondLargestSalePerMonth from abc.csv group by Month;
Load Month, max(Sales, 2) as SecondLargestSalePerMonth Resident abc group by Month;
drop table abc;

max1.PNGmax2.PNG

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.