SQL: Functioning in the Real World - Count
(Page 3 of 5 )
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.
Next: Count Distinct >>
More BrainDump Articles
More By James Payne
