Tuesday, December 11, 2007

Count of unique items and count of all items in the same query

If you've scanned this blog you'll know of my fascination for subqueries. This thread at UtterAccess.com caught my eye today. The poster needs to count disticnt entries in one column, and all entries in another column, all grouped by the value in a third column.
Sounded to me like three queries. One for the each count and one to produce the final result. The poster, though, challenged the UAers to do it in one.
Van T. Dinh's post is a nice clean all in one query SQL solution. It's a good example of taking the two or three queries you would have done separately and building them up into one query, essentially by surrounding each with brackets and then putting it in the FROM clause like a table.
There are a couple of other posts here on my blog that put the whole subquery in the FROM clause.

** Post links cleaned up 2011-09-05 **

No comments: