# Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

## 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 allselect 'C','Boston',29 union allselect 'D','Chicago',15 union all -- single #select 'E','NY',12 union all -- even #select 'F','NY',55 union allselect 'G','NY',57 union allselect 'H','NY',61go-- here's our query, showing median age per city: select city,AVG(age) as MedianAgefrom (select City, Person, Age, ROW_NUMBER() over (partition by City order by Age ASC) as AgeRank,COUNT(*) over (partition by City) as CityCountfromPeople) xwherex. Read more →

## GROUP BY ALL

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 allselect 1,2,'2008-02-25',6,2300 union allselect 1,1,'2008-03-02',23,610 union allselect 2,4,'2008-01-04',1,75 union allselect 2,2,'2008-02-18',52,5200 union allselect 3,2,'2008-03-09',99,2300 union allselect 3,1,'2008-04-19',3,4890 union allselect 3,1,'2008-04-21',74,2840SaleID CustomerID ProductID SaleDate Qty Amount----------- ----------- ----------- ----------------------- ----------- ---------------------9 1 1 2008-01-01 00:00:00. Read more →

## 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. Read more →

## SELECT DISTINCT and ORDER 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): Read more →

## 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 →

## 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. Read more →

## 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. Techniques to Avoid Read more →

## More on GROUP BY; Examining SUM(Distinct)

I've written a two part article on using SQL GROUP BY clauses over at SQLTeam.com. It's always a common topic of discussion and confusion amongst beginner and intermediate SQL programmers alike, so I thought I'd write a fairly long and hopefully comprehensive piece that takes a common summary report request and works towards the solution step-by-step. Read more →

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

Server: Msg 8120, Level 16, State 1, Line 1Column 'xyz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Read more →

## 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. Read more →

## 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 statement?  For example: Read more →

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

I thought I'd take a few minutes to discuss a general situation we see quite often in the programming world, using a simple T-SQL example. Let's say we have created a stored procedure that returns Customers from a database which allows you to optionally specify a particular Region to filter by. Read more →

## 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. Read more →

## 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. Always push GROUP BY aggregations as far into your nested SELECT statements as possible – if you have a situation in which you are grouping by long lists of columns that are not part of primary keys, you are probably have not structured your query correctly. Read more →

## 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. Read more →