SQL 101 - Summarizing Data (Part 2)

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.

About the Author: 

Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan. Connect with Shannon Lowder.

Posted in: 
Development

IT Labor Shortage: What To Do About It Part 1

The U.S. workplace has become a playing field of competition for hiring top talent in every industry, especially IT. Would you agree that there seems to be a massive and devastating shortage of IT skills and an aggressive war for global talent? This “brain drain” is making it more difficult to find people who are qualified to do the work that needs to be done. One would think that due to the job-killing recession that job seekers would be plentiful. Not so. Post-secondary education graduation rates complicate matters because they are sadly less than 50 percent.

In addition, the diminished rate of labor force growth is related in part to the reduction in the number of births in the United States. Today’s worldwide labor shortage was projected many years ago by the Bureau of Labor Statistics because of the impending retirement of baby boomers. Some people will argue that there is not a shortage of IT professionals, but simply a shortage of qualified IT professionals.

Either way, if you are a company seeking to hire IT professionals, you have an extraordinary amount of competition, so you will have to be well prepared to attract and keep the best of the best, or you will be out of luck. It’s your responsibility as the hiring manager to identify the right people who have more than technical certification, proven abilities, or specific skills. Your job requirements may also encompass work experience in project management, business process analysis, including architectures, process modeling and portfolio.

However, just as important as the required IT skills, you will need to hire job applicants with the energy, ambition, and potential it takes to meet your specific work standards as well as embrace a people-oriented leadership style and comfortably merge with your existing corporate culture. Personality counts, as does the ability and willingness to get along with everyone including internal customers and teammates. In conclusion, if you cite a lack of skills or experience as the reason for the IT talent shortage, you may want to re-evaluate your approach to recruiting, leadership and employee retention. Successful managers know their employees and what it takes to keep them motivated and on task. Successful organizations recognize the importance of building a corporate culture that attracts employees and potential new employees like a magnet.

Is this easy to do? What do you think? Are you willing to invest time (time is money), in going above and beyond as you plan for your future staffing requirements? Hit or miss recruiting is a promise of failure. And once you have the right people in place, you have to consistently respect, recognize and reward them for their contributions. It’s the “oxygen” that employees need to maximize their potential. In Part 2, I will discuss specific tips for attracting IT professionals that are difficult to find, and ways to keep them motivated to stick with you for the long haul.

About the Author: 

Carol Hacker has been a passionate instructor, engaging speaker and independent business consultant for over 25 years. As president of Hacker & Associates, headquartered in Atlanta, GA, she works with organizations to help managers, supervisors, IT professionals, team leaders and business owners meet the leadership challenge. Over 500,000 participants have benefited from her customized seminars on the topics of recruiting, retention, evaluating employee performance, change management, handling troublesome workplace issues and much more.

Posted in: 
Hiring Manager

Using PHP to Add Content to Plain Text

In my last blog message, I described how I use a converter to HTML-ize plain text. This is useful for taking URLs like http://www.eilertech.com/work/resume.htm and producing a link. Now that I convert each text file before display, I can add content.

One thing I add to my web pages, is ads. Some people pay me for specific ads. On other pages, I put in filler from Google Ads.

I based my converter on functions from snippets.dzone.com, particularly "txt2html". At the end of that function, when the HTML is almost ready, I can append to it. Or, since I want the ads up front, I pre-pend.

/* Ad from adsense.google.com.  Get your own ad from there, 
 * unless you want me to get paid for it! 
 * When you have the ad, they'll tell you what HTML to put in your site.
 * Cut and paste their text into the string below.
 * Put only double quotes inside the string, single quotes to define it. */ 
$html = '<' . 'table align=right> <' . 'tr> 
<' . 'td> <' . 'script type="text/javascript"><' . '!--
google_ad_client = "pub-4220969454236754";
/* 300x250, created 10/17/09 */
google_ad_slot = "6246428593";
google_ad_width = 300;
google_ad_height = 250;
//-->
<' . '/script>
<' . 'script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
<' . '/script>
<' . '/td> 
<' . '/tr> <' . '/table>' . $html;
		
return $html;

About the Author: 

Scott Eiler has for decades worked in all aspects of software engineering, in public and private sectors, in many different industries, on projects most people know by name, as employee, vendor, and now consultant. He also maintains his own diverse web site, including much commentary. Scott knows, engineering is more than just hacking out code.

Posted in: 
Development

Agile Project Management - Engaging Your Customer!

The agile methods come at software development by challenging many of our status quo practices. The first one is the engagement level of the ‘customer’. It’s my experience that most waterfall or traditional projects allow the customer to disengage after they start the project and provide an initial version of the requirements. After some time…later…they appear at the end of the project to receive their prize. Usually they’re disappointed in the end result—finding the functionality not living up to their original vision & expectations.

This sort of “end-points” behavior leads to many project failures due to a lack of clear communications, misunderstanding, and missed expectations.

Agile PM - Engaging Your CustomerBut it has a simple solution. The customer should stay engaged during the entire project. They should be available for trade-off discussions and for demo’s. They should provide ongoing feedback on interim deliverables. They should even understand the teams’ capabilities and implementation challenges. In a word, they should become a “partner” to the team and not simply a “stakeholder”. They need to have continuous skin in the game if the project heads south and they need to be a part of trade-off and scope adjustment decisions.

The agile methods have several ceremonies or tactics that are intended to draw the customer into the team; to foster their inclusion and to gain their insights. In this post I want to review and emphasize the importance of these practices and the need for overall customer engagement.

Backlog Grooming

You all know that a prioritized backlog of work items (features, tasks, key functional and non-functional requirements) is what drives the agile ‘machine’. The list is dynamic in nature with items being added, removed and changed nearly continuously during the lifetime of an agile project.

Many represent the central nature of the backlog as a pyramid or iceberg. It follows then that at the ‘tip’, the highest priority items are found. They are also defined at the clearest and finest level of granularity. In other words, these items are ‘ready’ for execution. The team has sized them and broken them down. They have talked about them several times as they’ve done this.

This activity is typically called ‘grooming’ the backlog. It’s where the team repeatedly revisits the backlog, refining it from multiple perspectives and getting elements ready for execution. The Scrum Product Backlog is not only a list of features, but it’s effectively a work breakdown structure for all of the work necessary to complete a product or project release.

By involving your customers and stakeholders in backlog grooming and making it transparent, you’re engaging them at a base level of requirement management and planning. Both are areas where your transparency can and should pay-off in increased interaction and feedback on the backlog—pre execution.

It also results in a better understanding on the part of the stakeholders on the level of complexity and difficulty that the team is encountering. I probably hear pushback 3-5 times in every grooming session around how easy they thought a feature would be to complete. Why, we almost do it now, so it can’t take more than an hour or so to extend the feature…right?

Then the team, usually patiently, tries to explain why the design is more complex and how the estimate for the story is extended by the “real world” they deal with every day. Or why a single day implementation might need three days of testing because of data security concerns. So having the customer involved in grooming and planning (next) can really help them understand why things take as long as they do, while also drawing them into powerful trade-off discussions.

Sprint Planning

Rarely does an approach to software development include customers and stakeholders in planning the project. At least not in the nitty-gritty details of the effort. But in Scrums’ Sprint Planning ceremony the customer is welcome to attend as the team considers the work and plans their execution.

The meeting has a two-phased approach. Phase one is focused towards the Product Owner sharing the sprints’ focus with the team. They review the body of stories targeted for this sprint and answer any late-binding questions the team may have about them. Quite often, the questions vary from specific behaviors to how the team might design and implement the feature set for the sprint.

Once phase one is finished and the team fully understands the work, they then dive-in and begin to break down each of the stories into work tasks. Keep in mind that these are ALL tasks associated with the work—for example: development, testing, design, inspections & reviews, documentation, etc. Every bit of work required to deliver the story to completion is identified by the team and the effort associated (hours) are estimated.

So you might ask, how does this help the customer engage? It allows them a view into the planning & execution dynamics of the team in order to deliver on their requests. They gain a glimpse into the level of difficulty associated with each feature—where are the hard ones, fraught with risk. And conversely, where are the easier ones.

They gain insight into the strategy the team will be using to deliver the features. Who will be working on which features and why? How is the team planning on handling dependencies and overall feature testing? How are they planning on handling risks? And if the team is operating as part of a larger group, how are they interacting with other teams on dependencies, integration, and collaboration?

In a word, the plans are totally transparent and open for discussion and adjustment. The team simply wants to deliver on its commitments, so constructive ideas are welcome—especially the empathy that stakeholders should realize by becoming part of the teams’ planning.

You see—software is rarely as simple or easy as most bystanders believe.

Daily Stand-up

All of the agile methods have the notion of a daily team meeting for sharing progress, challenges and making real-time adjustments to the teams’ committed goals. In Scrum this is known as the Daily Scrum and it’s where a customer can gain real-time insight into the “inner workings” of their agile team(s).

Agile Daily Stand Up

I remember a team implementing some features in an eCommerce application. To her credit, their VP of European Operations would attend the meetings whenever possible as they were developing the interfaces to Amazon UK. She would listen intently to the discussions, but wouldn’t interrupt the team with questions as she was a compliant ‘chicken’ in the stand-up meeting.

However, after the more difficult conversations in some of their daily scrums, she would pull me aside and ask me questions regarding what she had ‘heard’ in the stand-up. Were they in trouble? How could she help? And, could they adjust some of their scope commitments in order to assist the team? Were very typical questions she asked. In some cases, her concerns were unfounded. In others, they were absolutely right on.

The stand-up was her window into the teams’ work dynamics that she had never had before. No longer was she relying on written or e-mail status reports that were interpretations of progress. No! Now she received unfiltered, raw data from the team themselves.
She encountered the highs and lows. She clearly saw the teams’ challenges and, more importantly, how they approached resolving them.

She didn’t need a report to tell her where the risks were. Instead, she viscerally understood them by interacting with the team. She also saw the opportunities present themselves where she could assist the team in making adjustments while still achieving their & her overall goals.

It was incredibly powerful and frightening at the same time. It enticed, no encouraged, no demanded her to engage as an ‘owner’ with the team.

Sprint Review or Demo

One of the core agile manifesto points is “working software over jabbering about working software”, and I paraphrased a bit here. You see an incredible emphasis in agile teams, and I think it appropriate, to discuss most of your designs, requirements, and product commentary while looking at working software. There’s literally nothing else like it.

In Scrum, there is a Sprint Review or demo ceremony at the end of each sprint or iteration. In the demo, the team is responsible for showing off their working software that was focused towards their established sprint goal(s).

This ceremony is a “big deal” in Scrum teams. At iContact for example, we have sprint reviews for our teams every three weeks. We have them in our largest conference room. We literally invite the entire company (yes, we’re relatively small) to the event. Usually over half of our C-level team is in attendance and the room is typically standing room only.

Each team takes its turn to demonstrate their efforts for the past sprint. Team members all get a shot at speaking to their efforts. The audience is engaged. Asking questions and providing immediate feedback on the functions and features demonstrated.

Often the feedback extends beyond the demo. Folks will follow the team back to their seating areas and provide additional feedback and/or ask to see a particular feature again. I often see our CEO whispering his reactions to our product managers during and after the demo as he guides the vision he’s personally looking for in our product.

But beyond feedback for our teams, it shares information with our sales, customer support, and account managers. They become familiar with what’s being developed in real-time and can communicate those “coming attractions” directly to our customers.

Wrapping Up

One of the keys to engaging the customer is showing them you’re listening to their feedback. That it matters to you and you’ll take relatively immediate action on it. This loop of (Listen To Feedback – Develop/Change Software – Demonstrate – Listen Again) is a wonderful device for agile teams to assure they’re on the right track.

It also draws in their customers. It engages them in the process because they feel more like a partner in the teams’ efforts. Now not all customers will embrace this behavior. Traditional customers will be taken aback and you’ll hear excuses—mostly that they don’t have the time for it. You’ll need to be patient and, over time, draw them into your efforts. Working software that demonstrates solutions to their most challenging problems can be…well intoxicating.

Agile project managers maintain their teams’ focus on the heartbeat of delivering value and work hard to PULL the customer into the fray. Why? Because it’s their software, they need to care, and you need their feedback. So just do it!

Don't forget to leave your comments below.


Related Articles:

Agile Project Management—No Upfront Estimates!

Agile Project Management—Controlled Chaos


About the Author: 

Bob Galen is the Director, Agile Practices at iContact and founder of RGCG, LLC a technical consulting company focused towards increasing agility and pragmatism within software projects and teams. He has over 25 years of experience as a software developer, tester, project manager and leader. Bob regularly consults, writes and is a popular speaker on a wide variety of software topics. He is also the author of the book Scrum Product Ownership – Balancing Value from the Inside Out. He can be reached at bob@rgalen.com

Posted in: 
PM-Agile

SQL 101 - Summarizing Data (Part 1)

When beginning to learn SQL, it won't be long until you have to answer questions that require you to summarize the data. It's one of the primary reasons SQL is used. You store many records detailing events, then you can summarize that data and report it back to users, so they don't have to summarize it by hand.

I'm going to show you the 5 most common ways you'll be asked to summarize data: COUNT, SUM, MAX, MIN,and AVG (Average). There are several more that SQL understands, and once you learn to define your own functions, there will be an infinite number of ways to summarize that data. I just want you to gain an understanding of the basics, so you can go on to all those other methods.

COUNT

There are three variations to this function

COUNT(*)
COUNT(ALL expression)
COUNT(DISTINCT expression)

The first method is the most common. You'll use it when you want to count how many rows are in a table. If we ran the following query against our products table we've referred to throughout the SQL 101 lessons, we get 4, since we only defined four records.

SELECT
COUNT(*) AS [count]
FROM products
count
-----
4

We would get the same result from the following query.

SELECT
COUNT(ALL productName) AS [count]
FROM products

count
-----
4

If there were duplicates in the products table, and we only wanted a count of unique values, then the following version would let us count only the unique values in the table.

SELECT
COUNT(DISTINCT productName) AS [count]
FROM products

count
-----
4

Since there are no duplicates in our example table, we still get four.

SUM

Like COUNT, the sum function accepts ALL or DISTINCT as a modifier, but by default, it will sum all. Sum is the first function that really interprets the data. A COUNT is simple, a SUM actually does a little work for you. Warning, make sure the column your summing is a numeric type. The sum function will try to convert the column into a numeric type, and if it finds even one value that cannot be converted, you will get an error. You may also want to consider using the ISNULL function with this, since adding NULL to something is still NULL.

SELECT
SUM(price) AS [Total Price]
FROM products

Total Price
-----------
3.49

MAX, MIN, and AVG (average)

SQL is can be a great way to store lots of detail records. Whenever you have data, someone will eventually ask you to do some statistical analysis on that data. MAX, MIN, and average are the most common statistical numbers asked for.

SELECT
MAX(price) AS [max]
, MIN(price) AS [min]
, AVG(price) AS [average]
FROM products

max min average
---- --- -------
1.25 .25 .8725

Conclusion

Summarizing data will be a big part of your life as a SQL developer. These functions will serve as your first steps into learning more and more powerful ways to summarize data. If you have any questions, please, feel free to send them in! I'm here to help you learn as much as you can about SQL.

Other Recommended Articles:

SQL - The Advanced LIKE Clauses

SQL 101 - The WHERE clause

About the Author: 

Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan. Connect with Shannon Lowder.

Posted in: 
Development