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 →


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 →


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 →

More on GROUP BY; Examining SUM(Distinct)

I've written a two part article on using SQL GROUP BY clauses over at 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 →

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 →