SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

June 19, 2012 by Joey Blue Leave a Comment

SQL Count

The SQL Count is an important function that I use when I am exploring a new table.

I often use it to see how big a table is, but I also use it to determine counts within a group.

Let’s take a look at it’s uses.

Here is the employee table:

select * 
from employee; 

Results:

Select Results

You can tell this has 14 records, but imagine that it had 1,000,000 records.

You wouldn’t be able to just select * without it taking a while to bring all of the records back.

That is where the SQL Count comes in:

select count(*) 
from employee; 

SQL Count * Results

Here you can see it gave us one column with the count of the whole table.

Pretty simple.

Now, what if we wanted to find out how many people had a commission?

If we look above, we can simply count those people.  But, we have this new SQL Count function.

So, let’s use it:

select count(commision) 
from employee; 

Will we get 14 records, or 4 records?

Well, here are the results:

SQL Count Column Results

We only got 4 records.  Why is that?…

That is because the count function doesn’t count NULL records.  Again, if you look above, you will see a bunch of commissions that have the value of NULL.

This NULL value means we don’t have a value.  So, when the SQL Count  function looks at those rows, it ignores them.

But, look at the 10th row above.  It has a “0.00” value.  Why does it count that one?

Because, even though the value is 0, it is still a value (it isn’t null).

An that’s it.  That’s how you use the SQL Count function.

Filed Under: SQL Training Tagged With: Beginning SQL, SQL Function, SQL Select

June 18, 2012 by Joey Blue Leave a Comment

SQL Average

The SQL Average function is an easy function to use.

Let’s take a look at the Employee table:

select * from employee; 

Results:

SQL Average Results

If I want to find the average of the salary column, I would write the following query:

select avg(salary) as average_salary from employee; 

With the following results:

SQL Avg Results

Notice how we only get one column returned because that is all we asked for.

You can use the SQL Average on any numeric column using this technique.

When we get to the Group By clause, you will see how we can average across different groups of columns.

Filed Under: SQL Training Tagged With: Beginning SQL, Function, SQL Average, SQL Select

June 15, 2012 by Joey Blue Leave a Comment

SQL Functions

Today I want to talk about SQL Functions and how they are used in the SQL language.

Let’s start by looking at a simple employee table.

select * from employee; 

SQL Functions Result

This employee table has 14 rows.

Now, there are some built-in SQL Functions that allow you to average, sum, get the max and minimum and count. (There are others too, but I am only mentioning a few.)

These functions will look at a group of rows and perform the required action.

So, what if I want to get the average salary of all 14 of my employees?

Then I would do the following:

select AVG(salary) from employee;

The average function is actually just AVG and you put the column in between the parenthesis.

Here are the results:

SQL Avg Result

So the average salary is 2073.21…

That was pretty easy.  But, what if you want the column to have a name?

select AVG(salary) as average_salary from employee; 

You do what is called a column alias.  After you put the column in (in this case AVG(salary) ) you put in the “as” keyword, then you put whatever you want to name the column. (In this case I called the column average_salary)

And here is the result:

SQL Alias Result

And now your column has a name.

So now let me go ahead and use the rest of the functions I mentioned.

select 
AVG(salary) as average_salary 
,sum(salary) as sum_salary 
,min(salary) as min_salary 
,max(salary) as max_salary 
,count(salary) as count_salary 
from employee; 

And here are the results:

SQL Functions Result

Not too difficult?  But, I want to try one more thing.

I want to use the SUM function and the COUNT function to create my own average function.

If you remember from Algebra, the average is calculated by summing all of the values and dividing by the number of values.

So let’s give it a try:

select sum(salary)/count(salary) as my_new_average 
from employee; 

Notice how I took the Sum/Count.

Here is the result:

SQL Average Result

Notice that the value is the same as when we used the built in average function.  That is what we would expect.

So that is an introduction into SQL Functions.

Leave any question, comments, or topic suggestions bellow.

Filed Under: SQL Training Tagged With: Beginning SQL, SQL Function, SQL Select

June 7, 2012 by Joey Blue Leave a Comment

SQL Distinct

The SQL Distinct clause in the SQL Language allows you to remove any repeated data in a column.

Let’s take a look at the following table/query:

select * from customer; 

Here are the results from that query:

SNAGHTML1230953c

We have 6 rows and 4 columns in this table, but I only want to look at the customer_company column.

Here is the query to bring back just that column:

select customer_company from customer; 

With the following results:

SNAGHTML12327f0d

Now, I want to remove the repeated companies.  So if you look at “Dell” in the picture above, you will see that it is in there twice.

That is where the “Distinct” clause comes in.  Take a look at what happens when I put distinct into the following query:

select distinct customer_company from customer; 

I get the following results:

SNAGHTML1235ce36

Notice how we only have 5 rows.  That is because the extra/repeated Dell row has been removed.

So that is what the distinct clause does, it removes the repeated data elements in a column.

Please post any questions or comments below.

Filed Under: SQL Training Tagged With: Beginning SQL, SQL Distinct, SQL Select

June 6, 2012 by Joey Blue Leave a Comment

SQL Select Statement

The basis for retrieving data from a relational database like Microsoft SQL Server, Oracle, and MySQL is the SQL Select Statement.

The minimal SQL Select Statement if you were trying to get data from a table named “Customer” would be:

select * from customer; 

Here are the results from that query:

SQL Select Results

You can see that we have a table.  You might say it looks like a Microsoft Excel spreadsheet.

It has columns: customer_number, customer_name, customer_company, salesman_employee_number.

And, it has 6 rows.

So there are two keys to the Select statement above.

First:

select *

The “Select” word is a keyword that you start your query with.

The “*” means we want all columns from the table.

Second:

from customer;

The “from” word is a keyword that says you are getting ready to tell the database what table you want.

The “customer” word means you want that table.

The final thing to notice is the “;”.  This tells the database that you are done with your Select Statement.

It is optional in Microsoft SQL Server, but it is good to know about.

 

Now, if you only wanted to bring back the customer_name and the customer_company from the table,

the query (select statement) would be as follows:

select customer_name,customer_company

from customer;

Here are the results from that query:

SQL Select Columns Results

 

And that is the basics of the SQL Select Statement.

Post any questions and comments below.  I always read every comment.

Filed Under: SQL Training Tagged With: Beginning SQL, SQL Select

Recent Posts

  • SQL Database Normalization – 1NF, 2NF, 3NF, and 4NF
  • SQL Joins Tutorial for Beginners – Inner Join, Left Join, Right Join, Full Outer Join – SQL Training Online
  • Zillow House Price Analysis from CSV Archive – #PowerBI 002
  • Learn Basic SQL – 1 Hour Training Course – SQL Training Online
  • Create Table Statement in SQL Server and Inserting Baseball Homerun Leader Dataset – SQL Training Online

Popular Posts

  • SQL Functions
  • SQL Jobs in Oracle and Microsoft SQL Server
  • Troubleshooting a SQL Join
Copyright © 2023 · SQLTrainingOnline.com · Consulting at EmbarkBlue.com