SQL - Aggregate Functions Interview Questions

   

SQL - Aggregate Functions and Examples

Aggregate functions in SQL are used to perform the calculation on data. These functions are inbuilt in SQL and return a single value.

SQL Aggregate Functions Usage:

Generally from a business perspective, Top levels managers are usually interested in knowing whole figures and not necessary the individual details. 

>Aggregate functions allow us to easily produce summarized data from our database. 

For instance, say from a 'sales' database, management may require following reports

  • Least sold product variants.
  • Most sold product variants.
  • Average number that each product is sold in a month.
Take a Free Campus Placement Mock Test

We easily produce above reports using aggregate functions.

The ISO standard defines five (5) aggregate functions namely;

1) COUNT

2) SUM

3) AVG

4) MIN

5) MAX

In any interview, on SQL topic, you can expect atleast one question from the aggregate functions,

so lets first understand each of them and then look at few examples

1) The COUNT function returns the total number of values in the specified field. It works on both numeric and non-numeric data types. All aggregate functions by default exclude nulls values before working on the data.

2) MySQL SUM function which returns the sum of all the values in the specified column. SUM works on numeric fields only. Null values are excluded from the result returned.

3) MySQL AVG function returns the average of the values in a specified column. Just like the SUM function, it works only on numeric data types.

4) The MIN function returns the smallest value in the specified table field.

5) Just as the name suggests, the MAX function is the opposite of the MIN function. It returns the largest value from the specified table field.

Consider an orders table with the following structure.

OrderID OrderDate OrderPrice OrderQuantity CustomerName
1 12/22/2005 160 2 Smith
2 08/10/2005 190 2 Johnson
3 07/13/2005 500 5 Baldwin
4 07/15/2005 420 2 Smith
5 12/22/2005 1000 4 Wood
6 10/2/2005 820 4 Smith
7 11/03/2005 2000 2 Baldwin

Now, based on the above concepts there can be many types of questions like:

1. Count the number of orders made by a customer with CustomerName Smith made.

SELECT COUNT (*) FROM Sales WHERE CustomerName = 'Smith

2. In the above we used a filter condition, question could also be like, what is the count of total no of orders, for that we can simply use,

SELECT COUNT(*) FROM Sales

3.What if we are interested in the total value of all the orders?

We can use, SELECT SUM(OrderPrice) FROM Sales

4. More detailed analysis could be like, what is the average number of items per order? 

 We can use, SELECT AVG(OrderQuantity) FROM Sales

Note, we can combine all the above results with filters,  what if I need average order price when the price is more than 200.

SELECT AVG(OrderPrice) FROM Sales WHERE OrderPrice > 200

5.minimum price paid for any of the orders

SELECT MIN(OrderPrice) FROM Sales

6. maximum paid for any of the orders

SELECT MAX(OrderPrice) FROM Sales 

This was a brief intro into aggregate functions. All the very best. 

Take a Free Campus Placement Mock Test

 know more about Basic SQL Interview Questions and Answers

Read 1733 times Last modified on Saturday, 10 February 2018 15:20

1 comment

  • posted by Sanjay Kumar Saturday, 21 April 2018 15:47

    Hi There,


    Muchas Gracias Mi Amigo! You make learning so effortless. Anyone can follow you and I would not mind following you to the moon coz I know you are like my north star.



    We have full and transaction log backups stored on a shared unc path. I am looking for a way to delete files older than 15 days to free up space.
    I would also like to schedule it as job so it happens automatically.

    I look forward to see your next updates.


    Thank you,
    Irene Hynes

    Comment Link
Login to post comments

Vocabprep Banner

Please key in your e-mail address.

Be up-to-date with the happenings at Conduira!