Consider the following table, orders.

buyer             productName     purchaseDate   qtyPurchased     pricePaid

-------------     --------------- --------------- -----------     ---------

Shannon Lowder    pencil          1/1/2000         1              .25

Shannon Lowder    paper           1/1/2000         2              1.00

Shannon Lowder    Mountain Dew    1/1/2000         1              1.25

Shannon Lowder    pencil          1/5/2000         1              .25

Shannon Lowder    pencil          1/7/2000         1              .25

Shannon Lowder    Mountain Dew    1/10/2000        1              1.25

Shannon Lowder    Mountain Dew    1/11/2000        1              1.25

Shannon Lowder    Mountain Dew    1/12/2000        1              1.25

Shannon Lowder    Mountain Dew    1/13/2000        1              1.25

Shannon Lowder    Mountain Dew    1/14/2000        1              1.25

How would we find out the total number of pencils I purchased? You could do that with a WHERE clause. What if I wanted to see a quantity of each product I ordered? Now that's different. If you need to use an aggregate function and break down that aggregate by categories (or groups), then you're going to need to learn the GROUP BY clause. This clause will help you summarize data by these groups. So basically I'm going to teach you an add-on to the aggregate functions lesson. Let's dive right in.

Problem 1: Show me the total quantities purchased, broken down by product.

SELECT

     productName

   , SUM(qty) as [total quantity]

FROM orders

GROUP BY

   productName



productName    total quantity

------------    --------------

pencil		     3

paper		     2

Mountain Dew         6

Pretty easy, right? You can group by any one column or multiple columns. Grouping by multiple columns is a fundamental for most reports you'll be asked to write. You will eventually be asked a question like:

Problem 2: Show me a Report of Which Products Were Sold, by Month?

At first this seems more difficult, but really, all you'll have to do is add an additional column to your SELECT and GROUP BY list. I would like to take an aside here, and point out, sometimes business requirements aren't entirely clear. This example is a great case to consider ambiguity in a request. Is product name the "main" or first group, or is the month, the first group? In this case, we're going to use productName as the first group.

SELECT

     productName

   , MONTH(purchaseDate) as [Month]

   , SUM(qty) as [total quantity]

FROM orders

GROUP BY

     productName

   , MONTH(purchaseDate)



productName     Month	total quantity

------------    -----   --------------

pencil		1	3

paper		1	2

Mountain Dew    1       6

Since all the data in my example happened in January 2000, all the counts from before appear as they did before. This time we just have the extra column in the output. This is also the time where I will point out you can use functions in your GROUP BY clause, and as long as it's not an aggregate function (like SUM, MAX, MIN, etc) you can use it!

Summary

The GROUP BY clause is a fundamental part of reporting. Experiment with it, and become comfortable with it. If you have any questions, send them in! I'm here to help you learn all about SQL.

No, the title isn’t a typo, nor did I stutter. There’s incredible angst and debate over the agile methods nowadays. What works and what doesn’t? How do we run a pilot project? What types of team members are needed for agile projects? What sorts of companies, domains, and project spaces does it really excel in? And conversely, where it doesn’t? And how do you move from average agile to high performance agile?

From my perspective, that all makes a lot of sense.  While the agile methods are no longer in a bleeding edge, their adoption is still challenging fror the vast majority of organizations.  Partly due to the lack of perscriptive process guidance and partly because it's quite difficult to subtly "get it" and fully realize the essential promises of agility including:

• Vastly increased product quality and a focus on the customer
• Regular release tempos that are delivered no matter what happens
• Teams that are empowered & self-directed; and delivering minimalist and creative products
• Stakeholders who can make last-minute changes in strategy and focus
• Much more nimble and competitive organization; with agile disrupting the competition
• And yes, if executed correctly, a phenomenal increased speed and productivity play

Certainly, it takes hard work, skill, and knowledgeable guidance to achieve these results.  Guidance that isn't gained by reading books or attending by-rote, talking-head focused training sessions.  No!  Instead the learning is by doing agile.  By working in the trenches and by sharing lessons with experienced agile coaches.

A Novel Session to Explore YOUR Questions & Pain Points

So what's the point of this blog?

I'm giving a workshop at the MATRIX San Francisco office in early November that is unique.  Instead of canned slides and materials, we plan on gathering challenges and issues experienced in agile adoption from the audience.  So in true agile fashion, the content will be mined on-the-fly.  It will be tailored and focused towards the customer - you!  And as a collaborative group, we will mine the overall group for patterns and anti-patterns of how to effectively deploy the agile methods in such a way to achieve the above promises.

So each attendee has the opportunity to refine and focus the workshop towards them.  And to collaborate with Bob Galen, a seasoned and well-known agile coach who will be facilitating.

Come prepared to share  . . . your tools, techniques, and challenges.  Come prepared to learn.  But most importantly, come prepared to collaborate.  It should be a high value session.