SQL Aggregation queries using Group By, Sum, Count and Having

let's start off with a simple select query like this one that shows all fields from the grant table we get four fields and ten records now we can change our field select lists to select any fields we want we can go grant aim and amount and we get just those two fields we still get ten records now let's change this so we just get the M PI D we still get ten records even though one of them is null now let's look at these records we have employed seven employee to employee seven no now what if we wanted to see each individual value how many different values there are in other words we should see seven only once - only once ten only once so every value that appears one or more times we want to see it once well there's two ways to do that we can go distinct and then M PI D and we see no two three four five and notice seven appears only once or we have another alternative we can go group by M PI D and again we get every value once let's take this group by query and turn it back into a normal query for just a second and notice we get all 10 results from our table and let's pull in another field amount and run both of those now we see that even though employee 7 appears several times they have a different value each time what does employee 7s total whatever forty seven fifty plus eighteen thousand one hundred plus twenty five thousand is now here's a great usefulness for the group by if we still wanted to see only employee once we would group by the M PI D and what do we want to see for the amount next to them which one should it choose well perhaps we should aggregate them with a sum what is the total for each employees amount run this query employee seven has forty seven thousand eight hundred and fifty dollars the total for each employee is listed right next to the employee ID the employee ID is listed only once because we grouped on that and then the numeric amount was aggregated with a function you let's change this just a little bit let's say we wanted to figure out how many grants each employee found not the total well we would just change our aggregated function to count instead of sum and we can see employee seven has found three grams now what if I wanted to find the largest grant that every employee has found you change your aggregated function to find the maximum amount in that grouping and the highest grant that employee seven has found is $25,000 all of our queries throughout this book have set a great precedence of making sure that every field in the result set is named now this summarized query where I'm getting the grant grand totals next to the name appears to have no column name so we can easily just alias this as total amount and run it and then we get the field name let's do a group by query with a join let's start off with select star from employee as EMP and then inner join location as Lok and we'll join them on location ID equals location dot location ID and run it we get 12 records instead of all 13 employees because John Marsh Bank has a null location ID now let's change this to a full outer join and run it now I get a total of 14 records okay let's itemize our field list to just show city and first name and run it there's the city and Alex Seattle Barry Boston Li and so forth now let's group by the city because we're interested in seeing the total of how many employees work in each city so let's go group by city now we're aggregating on city are we trying to sum count find the maximum what are we trying to do for the first name we are trying to count all of the first names in that city notice in the city of null there's one person and in Boston we have 3 in Chicago we have none in Seattle we have 7 and in Spokane we have 2 when it comes to using count we actually have a couple of choices now before showing you those choices let's turn this back into a normal non aggregated query by just selecting the two fields and run this query now let's look at all of our results if we can Seattle is listed one two three four five six seven times each time there's a name next to it Spokane is listed twice now notice Chicago is listed once so there is one record of Chicago it just doesn't have anybody in it so if I were to count the first names Chicago even though it appears once in this query would be aggregated to zero let's see that again count first name and now we're going to group by city and run it Chicago has no values in first name that it could count now if I were to change this to account a strict it is no longer counting the values it's counting the number of times Chicago appeared as a record in that last result set and Chicago now has a 1 next to it so there's a difference between counting records and counting values if you're counting values you specifically name the field if you're just counting the records then you put an asterisk inside of the function okay got another trick to show you before turning you loose and that's this let's do a join between the employee table as impe and join it to the grant table as gr and of course those two join on the employee ID which is equal to the grant employee ID and we see the list of all the employees and the grants that they found vivid longing has found a grant if it can sin has found one so we have found by David longing and one grant found by David Kenson now if we were to do a group by first name followed by a count of the grant name we get four it shows david has found four but what it did is it grouped audits from David longing and the one record from Dave Kenson so in this situation me we might want to do a multiple level grouping so let me go first name comma last name and put last name up here as well which gets to another rule all of the fields listed in the group by clause should also be listed in the Select clause without an aggregated field that is not mentioned in the group by must use an aggregated function now let's test these results now we see Dave Kinson has one David lauding has three time for lab 4.1 skill check one query the employee table of j broco to see how many people work for each manager ID select the manager ID and count the employee ID field alias the field as M by D count when you're done your screen should resemble the figure you see here

Channel: Joes2Pros SQL Trainings

Views: 292,287

Rating: 4.8431878 out of 5

Keywords: Lab4, UsingGroupByForYoutube, ORDER, BY, COUNTING, GROUP, HAVING

Id: IVMfDpCGwK4

Length: 10min 0sec (600 seconds)

Published: Thu Jul 01 2010