Monthly Archives: February 2015

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.

Here’s a classic example that returns total sales per customer, in addition to returning the customer’s name and address:

C.CustomerID, C.CustomerName,
C.CustomerType, C.Address1, C.City,
C.State, SUM(S.Sales) as TotalSales
Customers C
ON C.CustomerID = S.CustomerID
C.CustomerID, C.CustomerName,
C.CustomerType, C.Address1, C.City, C.State

I can’t say how many times I see SELECT’s written this way and it is simply wrong. You should only be grouping on CustomerID, and not on all those other columns.  Push the grouping down a level, into a derived table:

C.CustomerID, C.CustomerName,
C.CustomerType, C.Address1, C.City,
C.State, S.TotalSales
Customers C
CustomerID, SUM(Sales) as TotalSales
CustomerID) S
C.CustomerID = S.CustomerID

Hopefully, you will agree that it is a much cleaner SELECT statement now, it is more efficient and it logically makes more sense when you look at it.

One more common mistake is that people just mimic the expressions in their SELECT list in the GROUP BY clause, without thinking logically about what grouping is really necessary.  For example:

SELECT LastName + ‘, ‘ + FirstName, … etc …
FROM Names
GROUP BY LastName + ‘, ‘ + FirstName

Again, that is logically wrong and also less efficient.  You should not be grouping by the expression itself; you should be grouping by what is needed to evaluate that expression. The correct grouping is:

GROUP BY LastName, FirstName

Too many people just keep stuffing column names and expressions into the GROUP BY clause until the errors go away without stepping back and thinking logically about how grouping works.  Take a minute to really consider what you need to return and how it should be grouped, and try using derived tables more frequently when writing aggregate queries to help keep them structured and efficient.


Create table ExamResult(name varchar(50),Subject varchar(20),Marks int)

insert into ExamResult values('Adam','Maths',70)
insert into ExamResult values ('Adam','Science',80)
insert into ExamResult values ('Adam','Social',60)

insert into ExamResult values('Rak','Maths',60)
insert into ExamResult values ('Rak','Science',50)
insert into ExamResult values ('Rak','Social',70)

insert into ExamResult values('Sam','Maths',90)
insert into ExamResult values ('Sam','Science',90)
insert into ExamResult values ('Sam','Social',80)


Returns the rank of each row in the result set of partitioned column.

select Name,Subject,Marks,
RANK() over(partition by name order by Marks desc)Rank
From ExamResult
order by name,subject

Rank() Example


This is same as RANK() function. Only difference is returns rank without gaps.

select  Name,Subject,Marks,
DENSE_RANK() over(partition by name order by Marks desc)Rank
From ExamResult
order by name

DENSE_RANK() Example

In RANK() result set screenshot, you can notice that there is gap in Rank(2) for the name Sam and same gap is removed in DENSE_RANK().


to get all participants that have more than 1 record in the table where at lease one of those records has IsCurrent = 0 and IsActive = 1

SELECT  ParticipantId 
FROM    Contact
        (   SELECT  1
            FROM    Contact c2
            WHERE   c2.ParticipantID = c.ParticipantId
            AND     ContactTypeId = 1
            GROUP BY ParticipantID
            HAVING COUNT(*) > 1
            AND COUNT(CASE WHEN IsCurrent = 0 AND IsActive = 1 THEN 1 END) >= 1

get records from north wind database(products table) grouped by categoryid and count >11 and min and max per group 

  SELECT ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY UnitPrice desc) AS ‘Row Number’, ProductName, UnitPrice,CategoryID
  FROM Products

  from (select categoryid,MIN(unitprice) as minn from CTE_Pages2 group by categoryid HAVING COUNT(categoryid)>11) A
  (select categoryid,max(unitprice) as maxx from CTE_Pages2 group by categoryid HAVING COUNT(categoryid)>11) B

using intersect

SELECT  ParticipantId 
    FROM Contact
   Group by ParticipantId 
  Having Count(*) > 1 
  SELECT  ParticipantId 
    FROM Contact
   WHERE IsCurrent = 0 
     AND IsActive = 1 
     AND ContactTypeId = 1

The SQL INTERSECT clause/operator is used to combine two SELECT

statements, but returns rows only from the first SELECT

statement that are identical to a row in the second SELECT

statement. This means INTERSECT returns only common rows returned

by the two SELECT statements.

Sharing Data in an Angular Controller or an Angular Service

Sharing Data in an Angular Controller or an Angular Service

This article is taken from John Papas website:

I received a great question about model data recently. A question I hear a lot that is about an extremely common situation that most Angular developers will face. The reason I think it comes up is that there are many examples showing different ways to code the situation, none of which are wrong, and none of which are absolutely right.

I found myself typing up the answer and realized that this is a great opportunity to share the thought process on how I think about these situations. You may agree with my conclusion, or you may disagree, and that’s cool. We’ll still be friends :) But I think what’s most important is to walk along the thought process so you can decide for yourself.

Sharing Data Among Controllers

Here is the question I received (in paraphrased format to shorten it)

Imagine we have a controller for the list of items. The Controller gets its data from some source such as a route’s resolve. Where should I store the data? Is it architecturally correct to store it in the service right after the controller has been initialized and clean up the service in $on('destroy') callback? Or should I store the data in the $scope, for example, and call the service for saving/getting the data?

Laying out the situation, I imagine a route that has a resolve function that might call a customerService.getCustomers. This resolve could then inject its results into a CustomersController. Once in the controller, the data can be filtered, edited, or whatever. Then when the data changes, do we send that changed data back to a service, maybe even the same service customerService, and have it make the $http calls to update it on the back-end?

Or another options the author lays out is keeping the data in a service where it can be shared. This type of service acts as a client side model. This scenario might have the resolve get the data from customerService, then add it to a client side model customersModel, and then inject the model into the CustomersController. This allows the controller to manipulate the data in a Singleton service that can be shared among controllers.

The author goes on to outline the differing opinions on his team …

“in my opinion, the second variant doesn’t allow to share (read or edit) the data from other controllers. However, my colleague’s opinion is that service should be stateless, more like $http. What do you think on this matter?

What do I think? I think you are both right. Your colleagues say services like $http should be stateless. Yes, absolutely services can be stateless and if they are built that way. But services can also act as models with state and there is absolutely nothing wrong with that.

Whittling it Down

OK, there are 3 scenarios I mentioned here:

  1. Controller hosts the model, customerService just gets and saves, no sharing between controllers
  2. customerService service gets the data and hosts the data, many controllers manipulate it
  3. customerService gets the data, another customersModel hosts the model, and a controller manipulates it
Controller is the Owner

The first option works fine and is pretty good for many simple scenarios. Simple isn’t bad, it just means you don’t need to share the data across multiple controllers. You can still share between controllers and sub controllers and directives via $scope. If you are fine with going and getting the data and being stateless, this is a good option. I’ve written a lot of code like this and it’s ideal for get the data, manipulate the data, save the data … next!

Service is a Model and a Service

This pattern has been used a lot of places. An object takes on the role of both getting/sending data and hosting the model for the data. I’m not personally a fan of this pattern, but it is an oft used pattern. I don’t like mixing these concerns, but it does work and you won’t be thrown into jail by the Angular police. If you go here, it works, and you can share the service between multiple controllers.

Model Service and a Separate Action Service

I’d rather have 2 services. I like one service that gets and sends the data (handles the verbs) and one service that acts as the model (the nouns). This allows for more re-use, more clarity, and frankly just feels better to me. This is the pattern I used in my Angular and Breeze courses on Pluralsight where I wanted to share the data across multiple controllers. Once I get customers, I want all of the Views to have access to those customers without hitting the server again. I have the choice to refresh, but I wanted to cache them in a local service. This allowed me to change in one place and the changes are seen in all controllers that shared the model.


It is worth pointing out that whenever you cache data and hold state you need to consider when to refresh that state. It’s not hard, but you need consider which types of data you want to cache and which you do not want to cache. Anything that is volatile, like inventories, you may not want to hold state on. However, lists of customers or states or stores are less volatile and could be hosted in state. Either way, I don’t let this bother me in my choice, I just make sure I handle the state properly and refresh when needed.

Ultimately the best answer is to consider the options and weigh them for your app’s needs. I hope this helps get you there.

What would I do? I would not make a service that is a model and a service. That’s just not my style. I would start with making a service get the data and have the controller manipulate it. If I had need of sharing the data, I’d consider making a model and going to the 3rd option I mentioned. If I had a need to share data and create a model, I likely would have need for a rich model and that’s where model validation and dirty checking could play a factor too. That’s when i’d go even further and add in Breeze.

But in short, start with the simplest: service gets data and gives it to a controller. Then move to a model based solution when you need sharing.

taken form(original article):

LINQ: building complex GROUPING queries utilizing joins deferred execution and anonymous types

var query =
from c in dc.Customers
join ct in dc.CustomerTypes on c.CustomerTypeId equals ct.CustomerTypeId
join pt in dc.PaymentTerms on c.PaymentTermId equals pt.PaymentTermId
//select ALL records variables for later use
select new { c, ct, pt  }

if ( filterByCountry )
  query = query.Where ( q=>q.c.Country == countryFilterValue )
if ( filterPaymentTerms )
  query = query.Where( q=> == paymentTermsFilterFieldValue )
if ( filterCustomerType )
  query = query.Where( q=>q.ct.SomeField == customerTypeFilterFieldValue )

//select the columns we need
var result =
from q in query
select new
   CustomerId = q.c.CustomerID,
   Name = q.c.CustomerName,
   Type = q.ct.CustomerType,
   PaymentTerm =


//joins of several tables

from c in dc.Customers

join o in dc.Orders on c.CustomerId equals o.CustomerId

join od in dc.OrderDetails on o.OrderId equals od.OrderId

join p in dc.Products on od.ProductId equals p.ProductId

join pc in dc.ProductCategories on


group new { c, o, od, p, pc } by c.CustomerName into grp

select new


CustomerName = grp.Key,

BeveragesCount = grp.Count ( g=>g.pc.Category == "Beverages" )

StationeryCount = grp.Count ( g=>g.pc.Category == "Stationary" )