Agregate functions in SQL

     Aggregating functions is used to find values out of our data in SQL. Say for example if we have a books database we can group our data by authors then find out how many total books an author has written  by aggregating that query. There are many different aggregating functions in SQL but the ones I am going to focus on today are COUNT, AVG, and MIN.

    The COUNT function allows you to count the total number of data in a specific row after we group our data. For example in the screenshot I attached we are using the books table. We are gathering all the first names and last names from the table. Once we do that we group by author first name that way any authors that have the same first name in our table. Once we do that we call the function COUNT(*) to count the number of duplicates in that row. That would give us the total number of books that specific author has written based off our database.

    The next function is AVG. This allows you to get the AVG of data that you group by. You would use it in a similar manner as I did in the COUNT function above however this would give you the average by adding up all of your data and dividing by the number of data that is grouped. I attached an example of a students table that I create that have been playing with. It is a bit complicated however we are going to focus just on the AVG(grade) part. We are calling for the columns first name, AVG(grade) and if it is null then it is automatically set to 0. Since we are grouping by the students first name all of the students grades will be grouped in that same row thus we can calculate the average of the grades. 

    The last aggregate function we are going to discuss is called MIN. Min allows us to get the row that has the minimum value of the row that we pass in the function. For example on or books table we have a column called pages. We can select the title column along with the MIN(pages) that will select the title that has the minimum amount of pages written in it. This is an extremely valuable function especially when we start to deal with data that is huge. In my books data I only have around 20 books total so it is not as big of a deal but when you start dealing with databases with millions of books like amazon then this tool would come in handy. 





Comments

Post a Comment

Popular posts from this blog

Ping Pong in Python

Image
      For this weeks assignment I decided to learn how to make a game using Python. I have been looking up online how to get better at coding and other than doing coding challenges its useful to create real world projects to put everything together. I learned so much just from doing this project that I didn't know from just doing coding challenges. Now this is 147 lines of code so I wont explain every single detail but I did leave comments on all of my code so you can go through it and understand what each block of codes adds to the game.I will also upload a video at the end of this page so you can see the game working in real time.     The first thing I had to do is import a built in library in Python called Turtle. It allows you to draw different objects and animations on to your screen. Once I installed Turtle I created a variable holding the Turtle.Screen function that allows me to create a screen that pops up. Once I did that I changed different attributes of my screen such as
Read more

Sunset Views Coding Challenge

Image
    For this weeks challenge I did a problem called sunset views. The challenge is that you are given a list of integers and also a string that is either west or east. The integers in the array represent the height of buildings. Your challenge is to return to the user a new list of indexes that represent the buildings in the list that were given that can see the sunset. I also included a video of me explaining my solution since this problem is easier to understand if I can show you visually my solution. In this portion I will explain how my actual code works.      To start I initialize a couple variables. First is tallest that represents the tallest building in our list, the tallest index, a new building list which will hold the indexes that can see the the sunset and the second tallest that will be initialized to zero. We will have a first loop that will be used to first find the tallest building in our list. We will use this tallest building in either west or east direction. Once we
Read more

Week 9 Coding Challenges

Image
      For this weeks challenge I did a coding challenge called generate document. The way this works is you are given a function that takes in 2 strings. The first string is called characters and the second is document. Your goal is to see if you can generate a document with the given characters in your string. The only way you can generate a document is if you have the same amount of characters in your character string that will enable you to generate the document. For example if characters = "abcabc" and document = "aabbccc" you can't make the document because you are missing one 'c' in the characters string. If you are able to make the document you will return True to the user else False. This program will also account for spaces to be able to generate the document.      The first thing I thought up when I saw this challenge was to automatically use a for loop. Once I did though I realized I needed a way to keep track how many instances of each charac
Read more