Software Enthusiast - KOY

I came across this post on the web and thought it useful to duplicate on my blog. The original post can be found here. ======================================================================= 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. Here's a classic example that returns total sales per customer, in addition to returning the customer's name and address: SELECT   C.CustomerID, C.CustomerName,    C.CustomerType, C.Address1, C.City,   C.State, SUM(S.Sales) as TotalSales FROM   Customers C INNER JOIN Sales S   ON C.CustomerID = S.CustomerID GROUP BY ...

I am an ardent fan of Joe Celko - goes way back to the early 90s and I am always looking for ways of horning on my SQL skills.  Who is Joe Celko? Well if you are into SQL and you've not heard or come across anything about Joe Celko, then, you must be from another planet! Well Joe Celko - in my view- is one of those PURISTS  tyring really hard to make SQL uniform accross board irrespective of the VENDOR. Simply put - any SQL Statement you can run against an ORACLE DB should be valid for a SQL SERVER db as well.  He tends to bring analytical reasoning into SQL and, again, in my view, one of the few mentors still around in the area of SQL. Anyway, I digress, but it is always good to stimulate the interest of the reader before delving straight to the issue at hand. The thrust of this article is that I came across a POST on the web and I was awe struck as to the simplicity as well as  the efficacy of the proposed solution. The problem was - in a set of rows assocate...