web 2.0

GROUPING SETS in SQL SERVER 2008



Introduction

GROUPING SET were introduced in SQL Server 2008, which allow multiple groupings to be returned in one record set. In this article I explained the answers for the questions which raises to everybody’s mind who hear this for the first time.


What is GROUPING SETS?

GROUPING SETS were added in SQL Server 2008 to give you greater flexibility when defining SELECT statements that include aggregate functions. We can fetch multiple groups set by executing just one SQL query. Although there are other alternates to get multiple group sets with one SQL query for example by using ROLLUP and CUBE clauses. But GROUPING SETS provide more flexibility than other alternates.


T-SQL Syntax for GROUPING SETS

SELECT 
     Column1, 
     Column2, 
     COUNT(Column3) As Alias_Name 
FROM Table_Name 
GROUP BY GROUPING SETS 
     ( 
        (Column1), 
        (Column2) 
     ); 


Examples of GROUPING SETS

The examples in this section provide you with an idea of some of the options available when defining GROUPING SETS. I have taken different examples here to explain different scenarios.


Using GROUP BY with GROUPING SETS

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS
    (T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

RESULT:


Using GROUPING SETS with composite elements

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2004'
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

RESULT:


Using GROUP BY with multiple GROUPING SETS

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID,T.[Group])
    ,(H.SalesPersonID)
    ,())
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

RESULT:


Using GROUPING SETS with a ROLLUP of part of the GROUP BY list

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
   ,ROLLUP(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

RESULT:


Using GROUPING SETS with a CUBE of part of the GROUP BY list

USE AdventureWorks;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
    ,CUBE(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

RESULT:


GROUPING SETS Equivalents

A GROUP BY clause that uses GROUPING SETS can generate a result set equvalent to that generated by a UNION ALL of multiple simple GROUP BY clauses. GROUPING SETS can generate a result equivalent to that generated by a simple GROUP BY, ROLLUP or CUBE operation. Different combinations of GROUPING SETS, ROLLUP, or CUBE can generate equivalent result sets.


GROUPING SETS Equivalents of a Simple GROUP BY
GROUP BY GROUPING SETS ( (C1, C2, ..., Cn) )
GROUP BY C1, C2, ..., Cn
GROUPING SETS Equivalent of UNION ALL
SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))
SELECT customer, NULL as year, SUM(sales)
FROM T 
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T 
GROUP BY year
GROUPING SETS ROLLUP Equivalents
GROUP BY ROLLUP (C1, C2, …, Cn-1, Cn)
GROUP BY GROUPING SETS ( (C1, C2, …, Cn-1, Cn)
    ,(C1, C2, ..., Cn-1)
    ...
    ,(C1, C2)
    ,(C1)
    ,() )
GROUPING SETS CUBE Equivalents
GROUP BY CUBE (C1, C2, C3)
GROUP BY GROUPING SETS ( (C1, C2, C3)
    ,(C1, C2)
    ,(C1, C3)
    ,(C2, C3)
    ,(C1)
    ,(C2)
    ,(C3)
    ,() )

References

MSDN - SQL Server 2008

Tags: , , , ,

SQL Server

How I Got Started in Software Development?



An interesting thread is going around asking how you got started in Software Development. It seems fun and since Mads tagged me, I thought I’d fess up as well.

How old were you when you started programming?

I guess I started programming when I was about 15 or 16 on my Pentium#3. It was nothing too wild. My big project was something I did for the Tech fair at college. At that time I have play with flash action scripting to design slid shows for the tech fair.

How did you get started in programming?

I was always interested in computers growing up and after much begging, my parent’s got me a Pentium#3 in 2000 when i was in 10th. I played tons of games on that thing, but I did begin to get interested in making them do more. When I had the chance to actually learn some programming at College, I knew I’d like it. So the Pentium#3 sparked the interest, and 1st Sem labs got me started doing much more than the very basics.

What was your first Programming Language?

My first programming language was C++.

What was the first real program you wrote?

Real? I guess that tech fair project was real as we have successfully executed the slides at the show.

What languages have you used since?

C, C++, Assembly, FoxPro, Visual Basic, ASP, C#, Vb.NET, ASP.NET, JavaScript, VB Script, XHTML, CSS, PHP, Objective C, Java(BB) and probably a few others I’ve missed.

What was your first professional programming gig?

My first programming job was with a Software development Company. I got a lot of experience there doing everything from requirement analysis, to managing projects, to programming and database management. It was a very interesting job and I stayed there for almost 5 years. I joined them as a trainee and left as Team Lead.

If you knew then what you know now, would you have started programming?

Probably. I still find software development challenge and rewarding. However, I was highly skilled in kicking a soccer ball as a freshman in high school. I was terrible at soccer, but I could shoot hard and place the ball well. If I knew that kicking field goals might make for a nice living, I might have worked in that direction a bit. With a bit of luck (ok, ok, a ton of luck), I’d be about to head off to training camp for my 15th NFL season with the San Diego Chargers. If that had not worked out, I’d probably fall back on Software Development.

What is the one thing you would tell new developers?

Read other peoples code and let them read yours. Reading code is an obvious statement as everyone has searched for a code sample of something or other. However, reading beyond a short snippet to see a larger picture and to learn new ways to do things, not just specific functions. There is just something about a reading through a well thought out project to see how the developers made it work. Also, don’t be afraid to show others your code. Ideally, you can check out ego at the door while you show your code to the other developers. While you might not like the feedback you get all the time, you will often learn new ways of looking at something as well as better ways to do things. Its all about continual improvement!

What’s the most fun you’ve ever had … programming?

Actually programming is my hobby. So every time i do programming i enjoy it. So it is difficult for me to point one. Probably building my first web site. I made a website, sumitgarg.net back in 2007. It may have been the simple website without any extra-ordinary functionality, but I had fun building it and running it. I planed for this website in 2007. At first, I just developed a simple website without any idea about blog. At that time, I thought I’d make a static 4-5 page site, so just to have a online portfolio. It just keep growing and growing and I never really planned it all out. Next, I added a database to allow my friends to log in to website and communicate with me. Shortly afterward, Blog was added. It went on and on. It was fun as I never knew what it was going to need next and often my friends would work with me helping me to see the best way for the site or admin features to work. My favorite feature was setting it up to integrate twitter and facebook widget. Its really nice to see all your friends contacted through one single website. Now i could see all activities at one place.

Tags: , , , ,

General

Data Type Synonyms

Introduction

This article basically illustrates the concept of Data type synonyms which are included in SQL Server 2005 for SQL-92 compatibility.

What are Data Type Synonyms?

Data type synonyms can be used instead of the corresponding base data type name in data definition language (DDL) statements, such as CREATE TABLE, CREATE PROCEDURE, or DECLARE @variable. However, after the object is created, the synonyms have no visibility. When the object is created, the object is assigned the base data type that is associated with the synonym. There is no record that the synonym was specified in the statement that created the object.
All objects that are derived from the original object, such as result set columns or expressions, are assigned the base data type. All subsequent metadata functions performed on the original object and any derived objects will report the base data type, not the synonym. This behavior occurs with metadata operations, such as sp_help and other system stored procedures, the information schema views, or the various data access API metadata operations that report the data types of table or result set columns.

List of Data Type Synonyms  

Synonym SQL Server system data type
Binary varying varbinary
char varying  varchar
character char
character char(1)
character(n) char(n)
character varying(n) varchar(n)
Dec decimal
Double precision float
float[(n)] for n = 1-7 real
float[(n)] for n = 8-15 float
integer int
national character( n ) nchar(n)
national char( n ) nchar(n)
national character varying( n ) nvarchar(n)
national char varying( n ) nvarchar(n)
national text ntext
rowversion timestamp

How to use Data Type Synonyms 

For example, you can create a table by specifying Data Type Synonyms:

 	CREATE TABLE Student(
	ID integer PRIMARY KEY,
	FirstName character varying(20),
	LastName national character varying(20),
	Marks Double precision)

FirstName is actually assigned an varchar(20) data type, and all subsequent metadata functions will report the column as an varchar(20) column. The metadata functions will never report them as a character varying(20) column. Similarly, LastName is an nvarchar(20) data type column and Makes is a float column.

Conclusion

This is an introduction to the Data Type Synonyms in SQL Server. The idea is to get familiar with another way of writing DDL queries using Data Type Synonyms.

Reference

MSDN

Tags:

SQL Server