Search This Blog

Loading...

Thursday, June 23, 2016

MS SQL Server - Window Functions - Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value, frequently used with the GROUP BY clause of the SELECT statement.

In this post, we will try using aggregate functions with and without OVER clause. I recommend you to read the previous post on Window Ranking Functions

Let create a sample table and insert some records.

declare @tblCustomerOrders table (
 CustomerName varchar(50),
 OrderAmount decimal(18, 2)
)

insert into @tblCustomerOrders (CustomerName, OrderAmount)
values 
('Imran', 100),
('Imran', 40),
('Kamran', 500),
('Salman', 350),
('Kamran', 430),
('Salman', 210),
('Imran', 70),
('Salman', 105),
('Imran', 750)

In this table, if we want to see the sum of OrderAmount for each customer, we use the following query.

SELECT 
  CustomerName
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders

But when you run this query you will get error:

Column '@tblCustomerOrders.CustomerName' is invalid in the select list 
because it is not contained in either an aggregate function or the 
GROUP BY clause.

Since we are missing the GROUP BY clause, we got this error. To work it as expected, we have to add GROUP BY clause with CustomerName field.

SELECT
  CustomerName
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders
GROUP BY CustomerName

Now you will get the expected result.

Lets see another scenario, if we want to list all customers with their SUM() of OrderAmounts, but we also need to list individual order amounts in separate column. Try this query:

SELECT
  CustomerName
 ,OrderAmount -- (individual OrderAmount)
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders
GROUP BY CustomerName

Since, OrderAmount field is not mentioned in GROUP BY clause, so you will get error message display:

Column '@tblCustomerOrders.OrderAmount' is invalid in the select list 
because it is not contained in either an aggregate function or the 
GROUP BY clause.

Now add OrderAmount field in GROUP BY clause along-with CustomerName field.

SELECT 
  CustomerName
 ,OrderAmount -- (individual OrderAmount)
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders
GROUP BY CustomerName, OrderAmount

This query will not generate error, but you get the following output, which is not the desired one.

Because each row is also grouped by OrderAmount, so the SUM(OrderAmount) is same as of OrderAmount.

How to fix this, there comes the OVER clause. Although there may be some other workarounds you can use to get the desired output, but the simpler one is using the OVER clause. You can put your aggregate functions in conjunction with OVER clause and there is no need to add GROUP BY clause.

Try getting SUM with OVER clause:

SELECT
  CustomerName
 ,SUM(OrderAmount) OVER() AS 'Sum'
FROM @tblCustomerOrders

This will generate the output:

But this is not the expected result, it is calculating the SUM of all records and not calculating SUM for individual Customers. As I also mentioned in my last post on Window Functions, add PARTITION BY clause in OVER clause.

The output will be:

As you might noticed, unlike the working query with GROUP BY clause discussed above, this query is displaying the list of all records along-with SUM, i.e. CustomerNames are repeating with no of occurrences exists in the table. So you can also add OrderAmount field to display along-with SUM filed.

SELECT
  CustomerName
 ,OrderAmount
 ,SUM(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Sum'

FROM @tblCustomerOrders

Generates the output:

This gives us the solution that we can list down all customer records with SUM of OrderAmount and also showing individual OrderAmounts.

Similarly we can use other aggregate functions like COUNT(), AVG(), MIN(), MAX()

SELECT
  CustomerName
 ,OrderAmount
 ,COUNT(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Count'   
 ,SUM(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Sum'
 ,AVG(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Avg'
 ,MIN(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Min'
 ,MAX(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Max'
 
FROM @tblCustomerOrders

Will generate the output as expected:

References:

Thursday, May 26, 2016

MS SQL Server - Window Functions - Ranking Functions

A window function is one that can be applied to a set of rows, The word ‘window’ is used to refer to the set of rows that the function works on. It provides a way to work with data grouped into logical windows.

The OVER clause used to define a window or set of rows within a query result set. Then we use a window function to computes a value for each row in the subset. OVER clause can be used to compute aggregated values such as average, sum, min, max, or a top N per group results.

The window function can be of two types:

  • Ranking function
  • Aggregate function.

In this post, I will discuss Ranking Window Functions.

Depending of on whether you’re using a ranking window function or aggregate window function, the OVER clause will need to be implemented accordingly. If we use the OVER clause with a ranking function, we can specify an optional PARTITION BY clause and a required ORDER BY clause. However, if we are working with with an aggregate function, then a PARTITION BY clause must be used, and should not have an ORDER BY clause.

Working with Ranking Window Functions

Ranking functions return an integer ranking value for each row or group in a partition/window.

Transact-SQL provides the following ranking functions:

  • ROW_NUMBER
  • RANK
  • DENSE_RANK
  • NTILE

Let see each function in action with an example.

  • ROW_NUMBER:

    Returns a sequential integer number for each row in the window.

    Example: Create a sample table and insert some records to query.

    declare @tblItems table (
     ItemCode varchar(5),
     ItemCategory varchar(50),
     StockQty int
    )
    
    insert into @tblItems(ItemCode, ItemCategory, StockQty)
    values 
    ('I001', 'Books', 10),
    ('I002', 'Books', 20),
    ('I003', 'Laptops', 25),
    ('I004', 'Laptops', 30),
    ('I005', 'Laptops', 20),
    ('I006', 'SmartPhones', 50),
    ('I007', 'SmartPhones', 30),
    ('I008', 'Books', 10)
    

    The following example calculates a row number for @tblTest based on ItemCode.

    SELECT 
     Itemcode
    ,ROW_NUMBER() OVER(ORDER BY itemcode) AS 'ROW_NUMBER()'
    FROM @tblItems
    

    Here is the output:

  • RANK:

    Define an integral rank value for each row in the window. It returns a same rank value for repeated column values, however, the next number in the ranking sequence is skipped by the number of repeating rows (also known as gap). It does not always return consecutive integers.

    The following example ranks the @tblItems with sort by ItemCategory. Since the ItemCodes I001, I002, I008 have same ItemCategory, these are tied, these are ranked one, and the next rank for item I003 is returned as 4, i.e. skipped the values 2 and 3 from the previous tied items.

    SELECT 
     ItemCode
    ,ItemCategory
    ,RANK() OVER (ORDER BY ItemCategory) AS 'RANK()'
    FROM @tblItems
    

    Here is the output:

  • DENSE_RANK:

    Define an integral rank value for each row in the window, without any gaps in the ranking. It also returns a same rank value for repeated column values. If values in the ranking column are the same, they receive the same rank. The next number in the ranking sequence is the next number in the sequence. The numbers returned by the this function do not have gaps and always have consecutive ranks.

    The following example ranks the @tblItems with sort by ItemCategory. Since the ItemCodes I001, I002, I008 have same ItemCategory, these are tied, these are ranked one, and the next rank for item I003 is returned as 2, i.e. it does not skipped the gap values (2 and 3) from the previous tied items.

    SELECT 
     ItemCode
    ,ItemCategory
    ,DENSE_RANK() OVER (ORDER BY ItemCategory) AS 'DENSE_RANK()'
    FROM @tblItems
    

    Here is the output:

  • NTILE:

    Divides the result set into the number of groups specified as an argument to the function. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

    The following example divides rows into 3 groups of items based on ItemCategory. Because the total number of rows is not divisible by the number of groups, the first two groups have 3 rows and the remaining groups have 2 rows each.

    SELECT 
     ItemCode
    ,ItemCategory
    ,NTILE(3) OVER(ORDER BY ItemCategory) AS 'NTILE(3)' 
    FROM @tblItems
    

    Here is the output:

    Let see another example of NTILE with group argument as 4. The following example divides rows into 4 groups of items based on ItemCategory. Now the total number of rows is divisible by the number of groups, so each group has equal number of rows (2 rows in each group).

    SELECT 
     ItemCode
    ,ItemCategory
    ,NTILE(4) OVER(ORDER BY ItemCategory) AS 'NTILE(4)' 
    FROM @tblItems
    

    Here is the output:

References:

Monday, April 11, 2016

TypeScript - How to import other TypeScript files.

As you proceed using TypeScript in large projects, you will end up with multiple typescript files. Then you may find a problem how we can use/import functions/classes from other files.

For example we have following interface in IShape.ts file.

interface IShape {
        x: number;
        y: number;
        height: number;
        width: number;
    }

And in another file Square.ts we have Square class with one of the constructor overloads is accepting that IShape interface as parameter.

class Square {
        public x: number;
        public y: number;
        public height: number;
        public width: number;

        constructor();
        constructor(obj: IShape);
        constructor(obj?: any) {
            this.x = obj && obj.x || 0
            this.y = obj && obj.y || 0
            this.height = obj && obj.height || 0
            this.width = obj && obj.width || 0;
        }

But if you try to compile this, you may get the following error:

Error TS2304 Cannot find name 'IShape'.

Because Square.ts don't know about ISquare.ts. To fix this error, we have to add reference to ISquare.ts file on the top of Square.ts.

/// 

class Square {
        public x: number;
        public y: number;
        public height: number;
        public width: number;

        constructor();
        constructor(obj: IShape);
        constructor(obj?: any) {
            this.x = obj && obj.x || 0
            this.y = obj && obj.y || 0
            this.height = obj && obj.height || 0
            this.width = obj && obj.width || 0;
        }

Now it will compile successfully.

You can add multiple lines of references to add multiple files. Just remember that path is relative to the current file.

Monday, March 28, 2016

TypeScript - Namespaces

TypeScript allows to define namespaces for logical grouping of code. Older versions of typescript use the keyword module while newer version can also used the keyword namespace which can be used in place of module keyword. Lets see namespaces in action in following example.
We have defined a class MySquare in namespace ShapesNamespace.
namespace ShapesNamespace {
    export class MySquare {
        public x: number;
        public y: number;
        public height: number;
        public width: number;
    }
}
Where namespace keyword defined namespace scope. Also notice the keyword export before the keyword class, it will expose this class to become accessible from outside of ShapesNamespace. If you do not add export keyword, you can not access this class outside of ShapesNamespace.
Now let use this class in another namespace.
namespace AnotherNamespace {
    var square1 = new ShapesNamespace.MySquare();
    square1.height = 10;
}
Here we are accessing MySquare class from AnotherNamespace with fully qualified class name. Intellisense will show available items in the code.
Another way to use code from other namespaces is to use aliases. Let see how we can use short names as aliases instead of typing more text in fully qualified names again and again.
namespace AnotherNamespace {
    import MySquareAlias = ShapesNamespace.MySquare;
    var square1 = new MySquareAlias();
    square1.height = 10;
}
We used the import keyword to set alias of ShapesNamespace.MySquare class, when we need to access that class, we can simply use its alias MySquareAlias instead of typing full name. Also intellisense works as expected.

Monday, March 14, 2016

TypeScript - Function / Constructor Overloading

TypeScript supports overloading of functions/constructor, but not in the same way as in .Net. Since JavaScript does not provide support of overloading itself, TypeScript enforces strong typing against the various overload signatures at design time, the function name is the same, but the signature of each overload (i.e. their name and parameters, but not the return type) is different. But we still have to write a generalized function that could work in generated JavaScript code, that is compatible with all overloads signatures, and will have only one function implementation.

This example make it clear:

interface IShape {
        x: number;
        y: number;
        height: number;
        width: number;
    }

    class Square {
        public x: number;
        public y: number;
        public height: number;
        public width: number;

        constructor();
        constructor(obj: IShape);
        constructor(obj?: any) {
            this.x = obj && obj.x || 0
            this.y = obj && obj.y || 0
            this.height = obj && obj.height || 0
            this.width = obj && obj.width || 0;
        }
    }

Square class has total 3 constructor signatures, first one is empty, second contains an object of type implementing interface IShape, and the last overload receives parameter with type any to make it compatible with previous overloads. Also notice that only the last overload function signature could have the actual implementation code.

In the calling stack, it looks at the overload list, and check the first overload to call the function with the provided parameters. If it finds a match, it picks this overload as the correct overload. For this reason, usually overloads should be ordered from most specific to least specific.

You can create objects with this class in the following ways:

    //empty constructor
    var square1: Square;
    square1 = new Square();
    square1.x = 10;
    square1.y = 50;
    square1.height = 100;
    square1.width = 100;
    
    var squareConfig: IShape;
    squareConfig = { x: 10, y: 50, height: 100, width: 100 };

    //contstructor with interface IShap
    var square2: Square;
    square2 = new Square(squareConfig);

    //contstructor with JavaScript object literal
    var square3: Square;
    square3 = new Square({ x: 10, y: 50, height: 100, width: 100 });

If you are working in Visual Studio with TypeScript support enabled, you will get intellicense support, and display avialable overloads similar to this:

Empty Constructor
Constructor with IShape parameter

Following function overloads added to the Square class, having different parameters of basic types.

 SetColor(x:boolean) : void;
 SetColor(x: number): void;
 SetColor(x: string): void;
 SetColor(x: any): void
 {
  if (typeof x == "boolean"){
   alert("Boolean overload is called, use default color value.");
  }
  else if (typeof x == "number") {
   alert("Number overload is called, use color from number value.");
  }
  else if (typeof x == "string") {
   alert("Number overload is called, use color from string value.");
  }
 }

Since we could have only one real function implementation, so inside this function body, we have to check for different types in order to respond with desired behavior.

Visual Studio Intellicense support will display overload options similar to this:

Overload with Boolean type parameter
Overload with Number type parameter
Overload with String type parameter

Above function can be called in the following ways:

    square1.SetColor(true);
    square1.SetColor(255);
    square1.SetColor("#235253");