Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In SQL Server 2008 Essential Training, Simon Allardice explores all the major features of SQL Server 2008 R2, beginning with core concepts: installing, planning, and building a first database. Explore how Transact-SQL is used to retrieve, update, and insert information, and gain insight into how to effectively administer databases. The course also covers features outside SQL Server's database engine, including technologies that have grown up around it: SQL Server Reporting Services and Integration Services. Exercise files are included with the course.
Next up, we have a few more keywords to work with an SQL and these would be grouped under the idea of something called aggregate functions, which is a fairly unpleasant name for a very useful piece. Now we've seen already how we can do a simple select statement like this, SELECT * FROM SalesLT.Product, and this returns everything. All our columns, all our rows and in fact, I can see it's 295 rows either by scrolling down to the bottom of the results or just reading it here in the status bar. But what if that was the piece of information I wanted? What if I just wanted to know the answer was 295? We have 295 products.
I don't care about the rest of the data. Well, I can do that by using a piece of SQL called COUNT. And what I'm going to do is give it a little argument here, a little bit of information that says I want the count of everything. SELECT COUNT(*) FROM SalesLT.Product. Execute and it basically returns just that value, just 295. I can see that I don't have a column name for it. If it was important for me to have a column name, I could perhaps return it as TotalProducts and execute it that way, but that might be necessary if I was sending this SQL back to a developer. But this is all I need, SELECT COUNT(*).
Now occasionally I'm asked, is it really important that I have the star, for example? Because if all I was doing earlier was bringing back ProductID From SalesLT.Product, that's 295 as well. So couldn't I just say SELECT COUNT of ProductID, instead of COUNT(*), and you can in fact. If I do that I'll get the same result, 295. You just need to be careful here because star will always give you the count of everything.
If you pick another column, so if I was to pick Color, which is a column in the Product Table, and execute that, well, in this case I get 245 instead of 295 because what it's doing is it's ignoring any null values that might occur. So as a general rule if you want a count of everything, it's SELECT COUNT(*). You can of course also use WHERE clause, as if you want to restrict the results to where products have a color of red. I could just type that in and execute it.
I still got the count and in this case it's 38. Now sometimes however the count isn't what you want. If you're looking at some of the data that you have, in this case, I'm going to back to selecting everything, I can see that I have a lot of products and they're all split up with different list prices and standard costs and weight and so on. But what if I wanted to find out what the maximum ListPrice was? Well, I could do an ORDER BY with the top one or I could do something like this. use the word MAX.
Now in this case I wouldn't say MAX(*) because it's not the max of everything. I want the maximum of the column ListPrice, hit Execute on that, and it tells me the most expensive thing was 3578.27. Well, if we have MAX, it's a pretty good guess that we're also going to have MIN and we do, 2.29, and we also have AVG. Give us the average price and if you want to of course you can start to throw some of these together, using several of these aggregate functions to in this case return the maximum and the minimum and the average price of our products.
Again, I don't have to use the AS keyword here. I just think it's a bit more useful if you're doing multiple functions that return different columns to give them all names. Now depending on the kind of data that you're working with, these may be what you are looking for. For example, if I open up my AdventureWorksLT database, I know that I have things like SalesOrderDetails and SalesOrderHeaders. I am going to just quickly SELECT * FROM SalesOrderHeader and by scanning some of that information, I can find things like the TotalDue and SubTotal and Freight amount.
Well, let's say what I wanted to do is find the maximum amount of all our sales orders. Of course, we can use MAX as we've done a little earlier, but what we can also do here is use the word SUM. SUM, instead of just counting them, will total them all up. When some people new to SQL kind of get a little mixed up between SUM and COUNT. Remember that COUNT is just going to give us the number of rows that had something in them.
In this case 32 whereas SUM is going to take the TotalDue column and add them all together, and that's often very useful when you're working with financial data. Now going back to the Product Table, I'm going to start off with something quite simple. Now we've seen how we can use the word COUNT to get a total number of products or add the WHERE clause to filter that down.
Well, what if I wanted to have the counts grouped by color? It's quite a common thing that what people will try and do is they will say well, perhaps I can say SELECT COUNT(*), Color, and execute that, and the problem is you're going to get this message that it can't give you Color because Color is not part of an aggregate function. You get this wonderful phrase it is not contained in either an aggregate function or the GROUP BY clause. If you see this message that says the GROUP BY clause, that's kind of a little heads up.
There is another part of SQL that can probably give you what you're looking for. What I'd like to have here is the different counts for the products with different colors or grouped together and I don't want to run that as several different SQL statements. So this is the way you do that. I want both the COUNT and the Color, but I need to tell SQL Server that you need to group the results by color. Once this GROUP BY clause has been added, it will allow this first column to be retrieved in that statement.
I execute that, and this is what we get in return: 89 Black, 26 Blue, 1 Gray, 8 Multi and 50 other products that have a NULL value in the Color column. Now if you start to use things like GROUP BY, you can also use some of the other aggregate functions as well. So I could start to kind of mix them up here, say for example MAX (ListPrice) and we're still showing the color and grouping by color, and in which case we're getting the maximum list price for each color.
So, very useful ways of getting to the important parts of your data, simply by using some of these aggregate functions.
Find answers to the most frequently asked questions about SQL Server 2008 Essential Training.
Here are the FAQs that matched your search "":
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.