T-SQL Tuesday #5: My First Cube

Kalen Delaney — Tue, 13 Apr 2010 19:00:00 GMT

It's time for the fifth T-SQL Tuesday, managed this time by Aaron Nelson of SQLVariations. Once again, the deadline came up just too quickly, and I'm on the road this week, so my entry will not be too long. Aaron's topic is reporting and in keeping with my past posts, this contribution will include a history lesson.

Since I first learned SQL, I've always thought of aggregation as a way of producing simple reports. Summary information was frequently all that was needed on an ongoing basis to see what was going on in an organization. Clients would want straightforward summaries of sum of sales per region or average orders per day or per month, or number of products available per category. Once a TSQL programmer was fluent with the GROUP BY operator, these simple reports could be generated very easily.

But what if we wanted just a bit more? What if we to see a summarization of total sales amount by region and salesperson, and also just by region?

Way back in the earliest versions of Transact-SQL before we had any 3rd party report writing products, any results we wanted had to be generated purely through T-SQL. We could use a clause called COMPUTE BY that allowed us to get aggregated values for any column in the SELECT list, and we could have multiple COMPUTE BY clauses for a single SELECT. This functionality gave us some ability to have multiple aggregations in a single query, but it had some drawbacks. First, we couldn't just return the aggregate information… we always had to return the detail rows also. Second, the rows that contained the aggregates ONLY contained the requested aggregates, which were based on a single column in the SELECT list; they did not include a value for the nonaggregated columns. This meant that that the result set was non-relational… the detail rows contained different columns than the COMPUTE BY rows. In fact, it was by understanding COMPUTE BY all those years ago that I learned exactly what was meant by relational result set. [COMPUTE BY is actually still in the product, even thought it produces non-relational results. However, the documentation for SQL Server 2008 indicates that it will finally be removed in the next version. ]

Having an interest in using aggregation for reporting, I was very interested in two new options to GROUP BY that were introduced in SQL Server 6.5. There were the CUBE and ROLLUP extensions. Their addition to the SQL language were described in a paper by Jim Gray in 1997, which you can read here:

http://arxiv.org/ftp/cs/papers/0701/0701155.pdf

This paper was a real eye-opener when I first read it 13 years ago. And it does an awesome job of explaining why a CUBE is called a CUBE. I'm reproducing one figure from this paper here, that shows the genesis of the cube, when grouping by three different 'dimensions'. The example in the paper is a car dealership, and the grouping values are year, make and color of vehicle.

Although I have not begun to explore analysis services or reporting services in any depth at all, I find that a thorough understanding of what is a cube and why we should care allows me to at least understand why these services are such powerful additions to the SQL Server product line.

~Kalen

T-SQL Tuesday #002: Non-aggregated columns in a GROUP BY query

Kalen Delaney — Tue, 12 Jan 2010 00:00:00 GMT

It's time for the second T-SQL Tuesday, once again managed by Adam Machanic. Since my first T-SQL Tuesday post dealt with my long years with the SQL Server product, I decided to continue in that vein. Adam asked for us to write something about a puzzle we had to deal with, involving TSQL. I decided to write about one of the most puzzling behaviors of the TSQL language, that fortunately went away many versions ago….

When I started working with Sybase, over 22 years ago, I was hired as a Technical Support Analyst. The company was new and small, and didn't have a lot of internal training procedures in place, so I was basically just given the manuals to read for a couple of weeks before being put on the phones with customers.

My only knowledge of SQL and relational database systems was from a one semester course I had taken at UC Berkeley, taught by Michael Stonebraker. But, at least that was something, better than nothing. I remember reading the TSQL manual, practically cover to cover, and encountering a very strange feature of queries using GROUP BY. I didn't completely understand it, but it seemed very strange. But I just kept reading, because I would have to face my first customer call… very soon. I remember that very first call, because I actually was able to provide some support. The caller was puzzled by the behavior he was getting from a certain TSQL query, the problem description sounded very much like something like the mysterious GROUP BY behavior I had read about just a day or two before. And I was able to solve his problem that day, on my very first call.

As good as my memory for SQL Server trivia is, I don't remember the exact query that the customer was calling about. (I do remember the customer though, as we went on to work together on many more interesting problems, and I have recently reconnected with him on LinkedIn.) Although I don't remember the exact query, I remember the behavior. It had to do with a TSQL 'extension' to the standard, that allowed you to use columns in a SELECT list containing aggregates, that were not aggregates and not part of the GROUP BY. Whew…

So let's see what I am talking about. I'm going to use the pubs sample database, which is the only one we had 22 years ago, and all sample queries in all the documentation were written to use it. I know that data very well. There is a table called titles, and each book in the titles table has an attribute called type, as well as title, price, and a few others. The following query is typical of the use of aggregation and GROUP BY. It takes all the rows with the same value for type, and produces one row of output for each distinct value of type. The average is the average per set of rows, one value per group of rows with the same type.

USE pubs;
GO
SELECT type, AVG(price) as avg_price
FROM titles
GROUP BY type;

RESULTS:
type         avg_price
------------ ---------------------
business     13.73
mod_cook     11.49
popular_comp 21.475
psychology   13.504
trad_cook    15.9633
UNDECIDED    NULL

Way back in SQL Server 3 and 4 (and 4.2 and 4.21) we could add additional columns to the SELECT, so I could add the title column, or the first 10 characters of the title, to the query. We would get the results shown after the query.

SELECT
SUBSTRING(title, 1, 10) as name,
type, avg(price) as avg_price,
count(*) as number
FROM titles
WHERE type like '%cook%'
GROUP BY type

RESULTS:
name       type         avg_price   number
---------- ------------ ---------- ------
Silicon Va mod_cook     11.49       2
The Gourme mod_cook     11.49       2
Onions, Le trad_cook    15.96       3
Fifty Year trad_cook    15.96       3
Sushi, Any trad_cook    15.96       3

So first, you might wonder what it means to add the extra column. Once the rows are grouped by type, adding some additional column basically undoes the grouping. Now I get individual data values instead of aggregates (summaries). Except that the avg_price and number do seem to be summaries. It might almost start to make sense, until we add an additional WHERE filter:

SELECT
SUBSTRING(title, 1, 10) as name,
type, avg(price) as avg_price,
count(*) as number
FROM titles
WHERE type like '%cook%'
AND title LIKE 'S%'
GROUP BY type

RESULTS:
name       type         avg_price   number
---------- ------------ ----------- -----------
Silicon Va mod_cook     19.99       1
The Gourme mod_cook     19.99       1
Onions, Le trad_cook    14.99       1
Fifty Year trad_cook    14.99       1
Sushi, Any trad_cook    14.99       1

Both the average and the count changed when we added the additional condition, but the data that comes back does not all satisfy the condition… there are titles that do NOT start with 'S'! So what's going on?

It turned out that once we broke the ANSI rules, and added a column to the SELECT list that was not part of an aggregate function and was not contained in the GROUP BY, then our WHERE clause changes its meaning. In this case, the WHERE was only used to determine what rows were used to compute the aggregates. This is why the values for avg_price and number changed with the addition of the second condition. If we wanted to control which rows come back, we needed to specify the condition in a HAVING clause.

SELECT
SUBSTRING(title, 1, 10) as name,
type, avg(price) as avg_price,
count(*) as number
FROM titles
WHERE type like '%cook%'
GROUP BY type
HAVING title LIKE 'S%'

RESULTS:
name       type         avg_price   number
---------- ------------ ------------ -------
Silicon Va mod_cook     11.49        2
Sushi, Any trad_cook    15.96        3

This sort of seems like the results I might expect. If this behavior is puzzling to you, imagine trying to explain it to customers and students new to SQL. It was actually my good friend Tibor Karaszi who ran all these queries for me on a SQL Server 4.21 that he had available, so I could verify the results. After being totally baffled by the behavior, he said: “I’m glad we don’t have to handle such topics in the newsgroups anymore!”

Even though we no longer can add unaggregated, ungrouped columns to the SELECT list of an aggregate query, that doesn't mean the puzzles are all gone. One problem that I continued to encounter when teaching beginning TSQL classes, was WHY this query isn't legal:

SELECT title, type, count(*) as number
FROM titles;

SQL Server 2008 gives you a somewhat helpful error message:

Msg 8120, Level 16, State 1, Line 1
Column 'titles.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Just a few versions ago, the message only reported that the titles.title column was invalid, with no explanation. In Books Online, the explanation was found in the section on GROUP BY. But if someone worte the preceding query, they didn't even use a GROUP BY and may have no idea there was any relationship of this query to grouping operations. It made for a very puzzling time, to try to figure out this query didn't work as expected.

Hopefully now there are fewer puzzling behaviors… but we'll just have to wait and see what everyone else blogs about.

Have fun (and THANKS Tibor!)

~Kalen

(posted Monday evening on the US West Coast, but already Tuesday UTC time.)

Search results matching tags 'T-SQL Tuesday' and 'aggregation'

T-SQL Tuesday #5: My First Cube

T-SQL Tuesday #002: Non-aggregated columns in a GROUP BY query