Home BrainDump Page 3 - SQL: Functioning in the Real World

# Count - BrainDump

A couple of months or so ago, I began talking about SQL functions and what they're used for. This article picks up where I left off. It defines the two types of functions, and begins to explain them in great detail.

 TABLE OF CONTENTS:
By: James Payne
Rating:  / 1
December 10, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE
 advertisement

Remember that crazy vampire from Sesame Street with the obsessive compulsive disorder? Always counting everything. One frightened child, two frightened children, AH AH AH!

SQL has its own Count, though this one isn't likely to lure your child into an alleyway and drain all its blood. It counts every row that does not have a value of Null and returns the value. Before we see what that looks like in code, let's add a new value to our table. Our new employee is going to have the Name Go-Go Roboto, have no listed age, and make \$100,000. When you are done, you table should look like this:

 Name Age Salary Ang Ree 92 \$12,000 Godzilla Nokamura 45 \$50,000 Mothra Fujiyama 27 \$80,000 King Kong Wong 19 \$100,000 Go-Go Roboto \$100,000

As part of the upcoming lawsuit, you might want to check to make sure that every employee in the database has an age listed. To do that, you could use the Count function:

Select Count(Age) from Employees

This would return the result:

4

That is because four fields contain data, and the fifth contains a Null value (or no value), and thus is not counted.

Of course, knowing the number of rows with good data is useless unless we know the number of total rows. To get this result, we would use the Count(*) function.

Select Count(*) From Employees

The result in this case would be:

5

Now that we have both the number of rows in our table, and the number of rows missing data from our Age column, we can do the math and know that one row has no data.

But we're not through with Counting yet! Remember the Count is OCD, and so are we. Let's fix the database by adding Go-Go Roboto's age, which is 29.

 Name Age Salary Ang Ree 92 \$12,000 Godzilla Nokamura 45 \$50,000 Mothra Fujiyama 27 \$80,000 King Kong Wong 19 \$100,000 Go-Go Roboto 29 \$100,000

The next step in our lawsuit will be to find out how many young whipper snappers work for the company. We can also use the Count(*) function for this:

Select Count(*) from Employees Where Age < 30

This will show us all of the employees aged 29 or less, which in this case, is 3.

Average age is 45, average salary for younger employees is way higher, and there are more youthful employees than old-timers...look out frivolous lawsuit, here we come.

 >>> More BrainDump Articles          >>> More By James Payne

blog comments powered by Disqus

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap