Aggregate Functions and like operators in SQL

-

Hello, and welcome to the blog. In this blog we are going to talk about "Aggregate Functions and like operators in SQL".

In previous blog we have seen inserting and retrieving data from database, today we will see more combinations in which we can retrieve data and we will also see some of the functions which help us in performing some calculations.

In this blog we will talk about :

  1. Aggregate functions
  2. LIKE operator

1) Aggregate Functions:

  1. Aggregate functions performs calculations on a set of values.
  2. Aggregate functions are also known as singled valued function because every time you use them you will get a single valued result.
  3. SQL has 5 aggregate functions :
    1. COUNT()
    2. MIN()
    3. MAX()
    4. AVG()
    5. SUM()
  4. One important thing is aggregate functions ignores null values( when you don't enter value of a column while inserting a record then that column has null value that actually means "no value") except when it comes to count() aggregate function it considers null value.

COUNT()

  1. COUNT function is used to calculate number of rows in your database table.
  2. It is used in a lot of places the easiest example I can tell you is when you open whatsapp then you can see the number of new messages which are there.
  3. Another example can be defaulter list from your college which tells you the count of lecture you attended.
  4. Let's see some example:
  5. select count(*) from Employees


MIN()

  1. MIN function is  used to calculate minimum value present in your column.
  2. You can use this function with varchar (string) data type as well. When you used it with varchar data type it just goes alphabetically that means 'A' is considered as minimum and 'Z' is considered as maximum. 
  3. Let's see some example :
  4. select min(salary) from Employee
  5. Now to understand how it works with varchar let's get all First Names present in our table:
  6. select FirstName from Employee
  7. Now let's select minimum first name
  8. select min(FirstName) from Employee
  9. As in our table "Bill" is the value whose first letter comes first alphabetically among all the value present, we have "Bill" as output.

MAX()

  1. MAX function is  used to calculate maximum value present in your column.
  2. You can use this function with varchar (string) data type as well. When you used it with varchar data type it just goes alphabetically that means 'A' is considered as minimum and 'Z' is considered as maximum. 
  3. Let's see some example :
  4. select max(salary) from Employee
  5. Now let's understand how it works with varchar.
  6. select max(FirstName) from Employee
  7. As in our table "Sara" is the value whose first letter comes last alphabetically among all the value present, we have "Sara" as output.

AVG()

  1. AVG function gives you average value of a particular column.
  2. If you try to find average value of a column with date, text, character you will either get an error or you might get 0 as result based on the DBMS software you are using.
  3. Let's see a example:
  4. select avg(Salary) from Employee
  5. I am using 'SQL Server Management Studio' let's see how it reacts when I use this function with a column which has varchar datatype.
  6. So, select avg(FirstName) from Employee
  7. I am getting an error which says "Operand data type varchar is invalid for avg operator"

SUM()

  1. SUM function gives you summation or addition of  a particular column.
  2. All the rules for AVG function applies to SUM as well. You will get same error if you try to perform this operation with text,date,characters,etc.
  3. Let's see a example :
  4. select sum(Salary) from Employee
Now, we are done with aggregate function. Let's see "LIKE" operator now

2)LIKE operator

  1. Sometimes what happen is we don't remember exact word you are searching for. For example you may not remember a persons name but you know that the person name starts with a particular letter or ends with a letter or contains a letter.
  2. So if this kind of situation arrives in the database, don't worry you got a help from "LIKE" operator.
  3. LIKE operator helps you find a pattern in column value.
  4. Let's see a example:
  5. select FirstName from Employee where FirstName like 'J%'
  6. In this query 'J' is the letter which should be there at starting of a string  and '%' represents any number of characters including zero character can be included in that value.
  7. So the possible outputs are : J, jj, ja, jb, jvfvdv, jvbgdvdfvcd, etc.
  8. Let's run this query in our database and check its value : 
  9. Now, what if we put '%' before our letter 'J' and remove '%' sign after it.
  10. select FirstName from Employee where FirstName like '%J'
  11. Now this will return all the values which ends with 'J'. Example: J, aj, bj, fgbfdgj, dfgfj, etc.
  12. Let's run this query in our database and check its value.
  13. We don't have any record ending with letter J.
  14. Now let's consider one more scenario, let's put '%' sign at starting and ending of the letter.
  15. select  FirstName from Employee where FirstName like '%J%'
  16. So it will return all the values which contains letter 'J'. It doesn't matter it comes first, last or anywhere it between.
  17.  Let's run this query in our database and check its value.
Congratulations on learning some small but very useful combinations for select. See you  in some another blog till then "Keep coding, keep learning because trust me you can make it big".

"Have a great day"

Comments

Post a Comment

Popular posts from this blog

SQL Joins | Inner Join | Left Join | Right Join | Full Join | Self Join

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about " Joins ". In the  previous blog  we have seen most important topic of Primary Key, Foreign Key and now its time to join some of the tables. So, In this blog we are going to talk about What is join and its purpose Type Of Joins Inner Join Left Join Right Join Full Join Self Join Let's  start with 1) What is join and its purpose There is a clause in SQL called Join. And its purpose is to combine rows from two or more tables which are connected with each other by a common column. When I say connected with each other by a common column I am talking about Foreign Key which we have covered in previous blog. Although if you haven't enforced foreign key for this blog but we do have a common column then also you can perform jo...
Read more

Update, Delete and Truncate Commands

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about "Updating and deleting data". We learn about "Inserting and Retrieving Data" in  this blog . Now its time to learn updating and deleting our mistakes. Yes with the help of the UPDATE and DELETE  statements  you can actually update and delete wrong entries in your database respectively. So in this blog we are going too talk about  UPDATE statement DELETE  statement TRUNCATE command Let's start with 1) UPDATE statement : UPDATE statement is used to update a specific record or a bunch of records which exists in your database table. If you made a mistake while inserting data in your database then UPDATE statement is the loophole through which you can correct it. Let's see a example: If you can see in our table I have made a mistake that SALARY  of "Sales Per...
Read more

More clauses in SQL

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about "More clauses in SQL". We learn about "WHERE" clause in this blog . Now its time to learn more clause which are going to help us to be more specific about retrieving data from Database and eventually its gonna make us more and more efficient in handling data in Database. So, In this blog we are going to talk about GROUP BY clause HAVING clause ORDER BY clause Now, let's talk about 1) GROUP BY clause This clause is often used with Aggregate Functions (which we have learnt in previous blog ) so that we can group our result with a particular column. I know it sound little confusing but we will solve this confusion with the help of example. Let's consider a scenario where I told you to count number of people working in each department with our existing Employee table. So then you might write a query like this: select count(*) from Employee where Department='IT' Then you will keep rep...
Read more