Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories





Grouping and summarizing data in SQL, using aggregate functions, etc.
How to calculate Median in SQL Server

Nothing earth-shattering here, I was just helping out a colleague with this so I thought I'd post up the example I gave him. -- sample table: create table People (     Person varchar(1) primary key,     City varchar(10),     Age int ) go -- with some sample data: insert into People select 'A','Boston',23 union all  -- odd # select 'B','Boston',43 union all select 'C','Boston',29 union all select 'D','Chicago',15 union all -- single # select 'E','NY',12 union all  -- even # select 'F','NY',55 union all select 'G','NY',57 union all select 'H','NY',61 go -- here's our query, showing median age per city: select city,     AVG(age) as MedianAge from (     select City, Person, Age,         ROW_NUMBER() over (partition by City order by Age...

posted @ Monday, August 30, 2010 12:00 AM | Feedback (1) | Filed Under [ T-SQL Techniques SQL Server 2005 GROUP BY ]


Here's an obscure piece of SQL you may not be aware of:  The "ALL" option when using a GROUP BY. Consider the following table: Create table Sales (     SaleID int identity not null primary key,     CustomerID int,     ProductID int,     SaleDate datetime,     Qty int,     Amount money ) insert into Sales (CustomerID, ProductID, SaleDate, Qty, Amount) select 1,1,'2008-01-01',12,400 union all select 1,2,'2008-02-25',6,2300 union all select 1,1,'2008-03-02',23,610 union all select 2,4,'2008-01-04',1,75 union all select 2,2,'2008-02-18',52,5200 union all select 3,2,'2008-03-09',99,2300 union all select 3,1,'2008-04-19',3,4890 union all select 3,1,'2008-04-21',74,2840 SaleID      CustomerID  ProductID   SaleDate                Qty         Amount ----------- ----------- ----------- ----------------------- ----------- --------------------- 9           1           1           2008-01-01 00:00:00.000 12          400.00 10          1           2           2008-02-25 00:00:00.000 6           2300.00 11          1           1           2008-03-02 00:00:00.000 23          610.00 12          2          ...

posted @ Monday, May 05, 2008 12:25 PM | Feedback (5) | Filed Under [ T-SQL GROUP BY ]

Simplify Your SQL with Variables and Derived Tables (or Common Table Expressions)

As with any programming language, it is important in SQL to keep your code short, clear and concise. Here are two quick tips that I find are very helpful in obtaining this goal.

posted @ Thursday, December 20, 2007 12:20 PM | Feedback (7) | Filed Under [ T-SQL Techniques Efficiency GROUP BY ]


Let's take a look at another one of those stupid, arbitrary SQL Server error messages that Bill Gates clearly only created because Micro$oft is evil and incompetent and they want to annoy us (and probably kill baby squirrels, too): Msg 145, Level 15, State 1, Line 4 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. This message pops up when you ask for DISTINCT rows for one set of columns, but you'd like to have the results ordered by one or more columns not specified in your distinct set.  For some reason, SQL Server will not allow...

posted @ Thursday, December 13, 2007 2:58 PM | Feedback (49) | Filed Under [ T-SQL GROUP BY Sorting ]

Some SELECTs will never return 0 rows -- regardless of the criteria

In SQL, the general rule of thumb is that the number of rows returned from a SELECT will be zero if your criteria did not match any data. However, there is an important exception to this rule: it does not apply when asking for aggregate calculations such as SUM(), MIN() or MAX(), without any grouping. read more...

posted @ Tuesday, November 13, 2007 11:11 AM | Feedback (11) | Filed Under [ T-SQL GROUP BY ]

The Mailbag: Referencing Assemblies in Reporting Services; some SQL help

As David Letterman would say, wake the kids, call the neighbors, it's time for The Mailbag!  Just some quickies today. Christopher writes: Greetings Jeff, First and foremost, great job with all of the blogs. I have a questions that I cannot seem to get a straight answer for. I am working with SQL Server Reporting Services (SSRS) and have the need to create VB functions to customize the reports generated. For example, a setter/getter to display information that would not be readily available from the query. SSRS allows this type of custom Visual Basic code to reside in the report itself, but since most of my code is across...

posted @ Thursday, October 18, 2007 12:19 PM | Feedback (0) | Filed Under [ Miscellaneous Report Writing GROUP BY Reporting Services ]

Group by Month (and other time periods)

When you need to summarize transactional data by Month, there are several ways to do it, some better than others. What to ultimately choose depends on your needs, but remember: Keep it short and simple in T-SQL, and always do all of your formatting at your presentation layer where it belongs. read more...

posted @ Monday, September 10, 2007 11:28 AM | Feedback (58) | Filed Under [ T-SQL GROUP BY DateTime Data ]

More on GROUP BY; Examining SUM(Distinct)

I've written a two part article on using SQL GROUP BY clauses over at read more...

posted @ Tuesday, July 31, 2007 12:44 PM | Feedback (6) | Filed Under [ T-SQL Links GROUP BY ]

But *WHY* Must That Column Be Contained in an Aggregate Function or the GROUP BY clause?

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

Arggh!! There it is, yet again .. that annoying error message. Why is SQL so picky about this? What's the deal!? read more...

posted @ Friday, July 20, 2007 10:33 AM | Feedback (35) | Filed Under [ Miscellaneous GROUP BY ]

Using GROUP BY to avoid self-joins

Sometimes, it appears that a necessary solution to common SQL problems is to join a table to itself. While self-joins do indeed have their place, and can be very powerful and useful, often times there is a much easier and more efficient way to get the results you need when querying a single table.


posted @ Tuesday, June 12, 2007 11:35 AM | Feedback (5) | Filed Under [ T-SQL Techniques Efficiency Joins/Relations GROUP BY ]

SQL Server 2005: Specifying Partitions for Aggregate Functions

Did you know that a new feature in SQL Server 2005 allows you to specify an OVER partition for aggregate functions in your SELECT statements?


posted @ Monday, May 21, 2007 2:52 PM | Feedback (7) | Filed Under [ T-SQL SQL Server 2005 Links GROUP BY ]

Better Alternatives to a FULL OUTER JOIN

As many of you know, I strongly recommend that you avoid using RIGHT OUTER JOINS, since they make your SQL code less readable and are easily rewritten as LEFT OUTER JOINs. In addition, I have yet to find a situation where a FULL OUTER JOIN makes sense or is necessary -- I have found that in just about every case other techniques work better.


posted @ Thursday, April 19, 2007 11:56 AM | Feedback (69) | Filed Under [ T-SQL Techniques Efficiency Report Writing Joins/Relations GROUP BY ]

Sometimes the problem isn't the code. It's the specs.

I thought I'd take a few minutes to discuss something we see quite often in the programming world, using a T-SQL example of a stored procedure that accepts a list of optional parameters allowing you to determine some basic filters on the results.


posted @ Wednesday, March 14, 2007 10:23 AM | Feedback (13) | Filed Under [ T-SQL Techniques GROUP BY ]

How to JOIN Multiple Transactional Tables in SQL

A common difficulty beginning SQL programmers encounter is joining two or more transactional tables all in one SELECT statement. Missing data, duplicates, time-out errors, and other unexpected results often arise from trying to directly write JOINS between two transaction tables.


posted @ Monday, June 19, 2006 3:34 PM | Feedback (10) | Filed Under [ T-SQL Techniques Joins/Relations GROUP BY ]

SQL GROUP BY techniques

One aspect of the versatile SELECT statement that seems to confuse many people is the GROUP BY clause. It is very important to group your rows in the proper place.


posted @ Wednesday, December 14, 2005 11:53 AM | Feedback (80) | Filed Under [ T-SQL GROUP BY ]

The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

When you have two tables (or resultsets from SELECT statements) that you wish to compare, and you want to see any changes in ANY columns, as well as to see which rows exist in 1 table but not the other (in either direction) I have found that the UNION operator works quite well.


posted @ Wednesday, November 10, 2004 9:29 AM | Feedback (152) | Filed Under [ T-SQL Techniques GROUP BY ]

Powered by:
Powered By Subtext Powered By ASP.NET