GROUP BY
Grouping and summarizing data in SQL, using aggregate functions, etc.
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.
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...
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...
I've written a two part article on using SQL GROUP BY clauses over at SQLTeam.com. read more...
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...
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...
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?
read more...
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.
read more...
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.
read more...
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...
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.
read more...
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...