Analytical Aakriti's Blog
Aggregate Functions in SQL for Data Analytics 📉

Aggregate Functions in SQL for Data Analytics 📉

Aakriti Sharma's photo
Aakriti Sharma
·

3 min read

Sum, average, count, min, max and all the quickies you need to pull out insights.

If you haven’t already, for a quick revision check out my previous blog for Basic SQL Commands and Operators.

[Operators in SQL for Data Analysis đź“Š
Operators are essential for data wrangling and filtering.medium.com](https://medium.com/@aakriti.sharma18/operators-in-sql-for-data-analysis-7296ab5103f9 "medium.com/@aakriti.sharma18/operators-in-s..")

Let’s get started today with slightly advanced clauses.

Aggregate Functions

Arithmetic operators only perform operations across rows. Aggregate functions are used to perform operations across entire columns.

COUNT

  • Counts how many rows are in a particular column.
  • Usage:
SELECT COUNT(*) FROM tablename
  • This returns one column , to alter the column name use AS.
  • COUNT(*) returns the total number of rows.
  • COUNT(colname) returns the number of non null values in the column. Column can be numeric or non numeric.
  • COUNT only returns the number of values present which maybe repeated, for count of distinct values use COUNT (DISTINCT colname).

SUM

  • Adds together all the values in a particular column.
  • Usage:
SELECT SUM(colname) FROM tablename
  • SUM can only be used for numerical values.
  • Treats NULL values as 0.

MIN and MAX

  • MIN and MAX return the lowest and highest values in a particular column, respectively.
  • Usage:
SELECT MIN(colname),MAX(colname) FROM tablename
  • They can be used on non-numerical columns.
  • MIN will return the lowest number, earliest date, or non-numerical value as close alphabetically to "A" as possible.
  • MAX does the opposite—it returns the highest number, the latest date, or the non-numerical value closest alphabetically to "Z".

AVG

  • Calculates the average of a group of selected values.
  • Usage:
SELECT AVG(colname) FROM tablename
  • It can only be used on numerical columns.
  • It ignores nulls completely i.e treats them as 0 for numerator and doesn't count them for denominator.

GROUP BY

  • To perform aggregation on subgroups of data according to a category specified.
  • Usage:
SELECT agg_func(colname)   
FROM Tablename   
WHERE cond   
GROUP BY colname   
ORDER BY colname
  • These groups can further be divided into subgroups by providing multiple columns to the ORDER BY clause like ORDER BY col1name, col2name,....

HAVING

  • To use conditions consisting of aggregation on subgroups.
  • Example MAX(col)> 1
  • Usage:
SELECT agg_func(colname)   
FROM Tablename   
WHERE cond   
GROUP BY colname   
HAVING cond1   
ORDER BY colname

CASE

  • SQL's version of if-else ladder with WHEN for condition checking and THEN specifies the value to be printed if the condition evaluates to true.
  • After this ladder ELSE specifies the default value if none of the condition evaluates to be true. It is an optional component.
  • CASE ends with END and then an alias is specified for the name of the computed column
  • Usage:
`S`ELECT col,.....,       
         CASE WHEN cond1 THEN val1         
         WHEN cond2 THEN val2         
         WHEN cond3 THEN val3         
         WHEN cond4 THEN val4          
         ......         
         ELSE defaultval   
         END AS newcolname  
FROM Tablename

That’s it for this blog! In the next part we’ll discuss about the super important concept of joints. Stay tuned ✨

Link to the next part :

[Joins in SQL 🤝
Joins are everything you’ll need when dealing with databases.medium.com](https://medium.com/@aakriti.sharma18/joins-in-sql-4e2933cedde6 "medium.com/@aakriti.sharma18/joins-in-sql-4..")

Interested in daily data analysis content? Follow me on Twitter and Medium.

Â