Jen McCown

I'd like to hold a little virtual celebration here, as we've just crossed over the threshold: we have more than 24 hours of training online!  That means, if you were so inclined, you could sit down and listen to us talk about SQL, PowerShell, .NET, HTML, interviewing, Windows, and more for a full day and night…and that's assuming you didn't stop for bathroom breaks!  The prospect is just thrilling.

Let's get to the five videos that put us over the top, shall we?

• First, we have Sean's Build .Net Objects in Powershell – I just love the topic of this video.  Here we show you how to create .NET code with powershell.  While we create an .aspx page, you can build any type of .Net object dynamically.  Use this process freely because it's just cool and easy and useful and full of awesome! 14 minutes.
• Next, two new DBAS @ Midnight videos, Good Interview Still Don't Get the Job: Part 1 (13 minutes) and Part 2 (14 minutes) – We discuss some reasons why you may still not get the job even though you had an excellent interview.  This is NOT your stale old stuff…"be sure to bathe, bring your resume, wear a suit, don't insult the HR girl's mother".  This is our experiences, and thoughts on factors that are sometimes out of your control.  Plus we mention Star Trek.
• And two more new DBAs @ Midnight vids, Keeping your Sanity when out of Work: Part 1 (16 min) and Part 2 (15 min) – Here we're discussing some ways of keeping your sanity and your confidence when you're out of a job, so you can ensure you get that next gig that comes along.  By the way, if you download these and play them in fast mode in Media Player (CTRL-Shift-G) it takes about half the time, and you can giggle at how funny we sound.

-Jen McCown, http://www.MidnightDBA.com

“Level  Zero” doesn’t mean anything, it’s another Kung Fu Panda reference. “Iiiiii’m not an expert, let’s just start with zero…level zero.”

Note: This article is the companion to the video Grouping Basics: Level Zero.

You use a SELECT statement to get rows of data from the database. 

select ID, gameTime, thingEaten, thingTypeEaten, pointsGained from pacman order by gameTime 

One of the most basic things you can do with that data is to group it into sets of like data, in order to summarize the data. For example:

select thingEaten, thingTypeEaten, pointsGained from pacman GROUP BY thingEaten, thingTypeEaten, pointsGained  

Each row above represents one or more rows in the table that have the same thingEaten, thingTypeEaten, and points.  Right now we don’t know HOW many tables are in the database that have, say, bigDot, dots, and 50 points.  We use aggregate functions to get more information about our groups.  Like COUNT, which gets the number of rows in a group, and SUM, which totals a column in the group. For example:

select thingEaten, thingTypeEaten, pointsGained, count(*) [Count], sum(pointsgained) [Sum]from pacman GROUP BY thingEaten, thingTypeEaten, pointsGained

ORDER BY thingTypeEaten, thingeaten

A couple of notes about the query we’re using: first, I’m using an ORDER BY. You don’t have to, certainly, but it makes your results more readable.  Next, it’s a good idea to use surrogate column names when you use aggregate functions.  I’m calling mine Count and Sum, just to be really creative.

Also, notice that the SELECT statement looks like the GROUP BY, plus aggregates.  This is because GROUP BY requires that each item in the SELECT statement must be included in the GROUP BY, or must be an aggregate.  To take an example of this, let’s try adding our primary key “ID” to the select statement:

select ID, thingEaten, thingTypeEaten, pointsGained, count(*) [Count], sum(pointsgained) [Sum]from pacman GROUP BY thingEaten, thingTypeEaten, pointsGained

ORDER BY thingTypeEaten, thingeaten

We receive this error:

Msg 8120, Level 16, State 1, Line 1

Column 'pacman.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

We tried to return individual row information without grouping it; we essentially gave the compiler an impossible task, that is, “Go combine these rows into one summary row, using type and points as the differentiator. Oh, and while you’re at it, include each individual row ID in that group.”  It doesn’t make sense, it can’t be done (like this), so SQL rightfully blows you a raspberry.

Notice that you can control the level at which you group. Right now we’re grouping by thingEaten, thingTypeEaten, and pointsGained. If we wanted to, we could get a higher-level view of our Pac-Man game by grouping only by thingTypeEaten, and totalling our points.  Remember to remove thingEaten and pointsGained from every clause in your query: select thingTypeEaten, count(*) [Count], sum(pointsgained) [Sum]from pacman GROUP BY thingTypeEaten

ORDER BY thingTypeEaten

There you go, that’s the very basics of grouping.

To review:

·         GROUP BY allows you to collapse like sets of rows into a single row

·         Use aggregate functions to get more information about each group. Examples include COUNT, SUM, AVG, MIN (minimum), MAX (maximum), and so on.

·         ORDER BY is useful for readability, but not required.

·         GROUP BY requires that each item in the SELECT statement must be included in the GROUP BY, or must be an aggregate. 

·         Get a more detailed summary by grouping by more columns; get a higher level view by grouping with fewer columns.

-Jen McCown, http://www.MidnightDBA.com