Day 8: COUNT, SUM, AVG, MIN, MAX Functions In SQL

Day 8 Of Learning

Image By Author

Hey Guys, hope you are good.

Hope you guys had a lovely weekend.

Hope you guys are following my posts and my content is working on your learning effectively. If not, do comment.

So lets get on with today’s topic. Its pretty important as when working with data you will need these concepts every now and then to aggregate your results.

Lets get started.

Use Of COUNT In SQL

COUNT is an aggregate function used to count the number of rows/entries related to your data. For example, I need to know the count of customers in a month I’ll use COUNT, however when looking for customer in a specific month or customers for a specific product I can add a WHERE clause.

So lets get started.

Image By Author

We can see that the total number of games we have is 3916.

Now I want to check the number of games released on PS4 that were sold.

Games released on PS4 sold = 583.

Now lets move on.

Use Of SUM In SQL

These functions are pretty easy to understand and straightforward. SUM as you know means addition and in SQL it means SUM whatever column you want see the added result of.

Image By Author

Total Sales of PS4 Games are 3.3 Billion.

Correction: Column name should have been PS4_Games_Sales but I named it PS4_sales in haste.

Use Of AVG In SQL

AVG function in SQL is used to calculate average of your column. It can be used to see average sales over a platform, time period, etc.

Here we’re going to see the average sales of Grand Theft Auto V per console.

Image By Author

GTA V sold an average of 16 Million copies per console. Way to go, Rockstar Games!

Now lets view the minimum sales and maximum sales of GTA V.

Use Of MIN And MAX In SQL

MIN and MAX as obvious are used to see minimum and maximum values of a column (in this case total_sales).

We’re going to query and see what was the minimum and maximum sales that GTA V did and on which console.

Lets get started on the query

Image By Author

The query is a little complex because we wanted to see the specific console in which we have minimum sales. Now lets do the same for maximum sales.

Image By Author

So PS3 has the most sales i.e 20.3 Million sales while only 8.7 Million copies were sold for Xbox.

So, that’s it we’re done with some very basic functions of SQL. I know I’ve been using group by and haven’t done a post on it, but we’ll look into it tomorrow. So stay tuned.

Exercise

Here’s what you need to do today

1. Load the data from UK train rides

2. Query the maximum and minimum price paid by credit card

3. Count the total number of transactions

4. Query the average price of tickets at London Kings Cross station

5. Query the total revenue generated at London Paddington station

6. Comment your results below

7. If you’d like to share your queries I’m making a thread on the chat please share your queries there instead of the comments.

If you have any confusions or you cant complete the exercise or did not understand the concept of these functions, leave a comment below.

---

I am a BI Engineer working at a Data And AI Firm. I’m trying to help newbies in Data land their first data job and impact as many fellow data professionals as I can. Here’s my contact info:

1. ajmal7809@gmail.com

2. Linkedin

3. bideveloper_ (Discord)